CODEWITHSUNDEEP
excelvba
user1996971
user1996971

Reputation: 543

Looking to write vba code to combine data from two Excel worksheets into one

I understand that the question is pretty broad, but I'd appreciate any help you can give.

The task at hand is to take data from a very messy worksheet (called "Monkey") and sort it into another worksheet (called "Master"). There is one column called "name" which is the only constant between both spreadsheets. I want to write a macro that will take the first row of the "constant" column, find that same row in the constant column of Monkey, and then begin copy-pasting the relevant information.

What I have so far is:

Sub Test()
    Dim Rng As Range
    Dim i As Long

    i = 2
    Application.ScreenUpdating = True
    While i <= 133
        Set Rng = Range("E" & i)
        If Rng = Worksheets("Monkey").Rng(, -16) Then
            Range("E138").Select
            ActiveCell.FormulaR1C1 = "Success!"
        Else
            i = i + 1
        End If
    Wend
End Sub

--

This is just to test the part of the vba code that will match (printing 'success' if the match is found') The actual copy-pasting subs I'll write myself, but for now, can anyone help with this loop? The constant column is E on master, and U on Monkey. Would setting E2 as a range work, allowing the 2 to increase each time the right row isn't found?

I'm willing to do a lot of experimenting myself, so even the tiniest pieces of advice will help greatly.

Upvotes: 1

Views: 675

Answers (1)

Maxime Port&#233;
Maxime Port&#233;

Reputation: 1074

You can start with something like that:

  • Define the constant name range of Master and Monkey
  • For each value of Master, search the monkey equivalent

  • If monkey equivalent is found, do what you want

    Public Sub Test()
    Dim cellMaster As Range, cellMonkey As Range
    Dim rngMasterCst As Range, rngMonkeyCst As Range

    ' Set constants columns from master and monkey
    Set rngMasterCst = Worksheets("master").Range("A1:A500")
    Set rngMonkeyCst = Worksheets("monkey").Range("A1:A500")

    ' For each constant of master
    For Each cellMaster In rngMasterCst
        ' Search for the value in Monkey
        Set cellMonkey = rngMonkeyCst.Find(cellMaster, LookIn:=xlValues)

        ' if the value is find
        If Not cellMonkey Is Nothing Then
            ' Here cellMaster and cellMonkey have the same "constant name"
            ' Navigate in the row with the help of .Offset()
        End If
    Next
End Sub

Improvements:

  • Use .end(xlDown) on range to find the last row instead of set it static

Upvotes: 1

Related Questions