Nested loops question VBA

P

ProbioT

New member
Joined
Jan 12, 2019
Messages
10
Reaction score
0
Points
1
Excel Version(s)
2007
Hello everyone,
I am trying to make to copy IDs from one sheet ("Master") to the other ("Report") only if cells from columns B, C and D match.
So far I made it work but only if cells from column B match (need to add to match C and D, too).
My work is based on a tutorial from the internet but I do not know how to modified to my needs. Nested loops are not my cup of tea.

Any help is much appreciated.

Thanks.
 

Attachments

  • TransferQuest.xlsm
    17.2 KB · Views: 6
You don't need further nested loops, you just need to extend the If test.

You also don't need to copy columns A:D, B:D are already populated, so you only need column A.

Code: 
Sub transfer_ID()

Dim i As Long, j As Long
Dim report As Worksheet
Dim lastrow1 As Long, lastrow2 As Long
Dim myName As String
Dim myAisle As String, myBox As String


    Application.ScreenUpdating = False
    
    Set report = Worksheets("Report")
    lastrow2 = Worksheets("Report").Range("B" & Rows.Count).End(xlUp).Row
    report.Range("A2").Resize(lastrow2 - 1).ClearContents
    
    With Worksheets("Master")
    
        lastrow1 = .Range("B" & .Rows.Count).End(xlUp).Row
        For i = 2 To lastrow1
        
            myName = .Cells(i, "B").Value
            myAisle = .Cells(i, "C").Value
            myBox = .Cells(i, "D").Value
            
            For j = 2 To lastrow2
            
                If report.Cells(j, "B").Value = myName And _
                   report.Cells(j, "C").Value = myAisle And _
                   report.Cells(j, "D").Value = myBox Then
                
                    .Cells(i, "A").Copy report.Cells(j, "A")
                End If
            Next j
            
            Application.CutCopyMode = False
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub
 
Last edited:
[h=1][/h]“Knowledge is power.” Your code works perfectly as I'd like.
Thank you very much for your quick response and the solution for my excel problem.
You have motivated me to learn even more about VBA. It is a real power.:redface:
[h=1][/h]
 
You must log in or register to reply here.
Back
Top