VBA find match value using loop

Question

Hi everyone,

I like to using loop to import information on Master worksheet to the Report worksheet if there found match. Image below is show how is the data set up.

On the Report Sheet, the only data I have is the list of Item number on column A, then I wan to check value of each cell compare with value of each cell on column A of Master Sheet (there will be no duplicate value on these 2 worksheets).

If found matched value then:

• Copy value on Column B of Master Sheet to Column D of Report Sheet.
• Copy value on Column D of Master Sheet to Column E of Report Sheet.
• Then keep loop to the last row of column A on Report Sheet.

If match value not found then:

• Assign the text NOT FOUND to column B and Column D of the Report Sheet.

   

I just create this example start from column A but the Master data I received is not on that column, I simulate this way to make it easy to explain the situation, therefore the VLook Up function might not able to use in this case.

Would you please help. I'm very new with loop in VBA, please help. Thanks in advance!

Tony

Answer 1

Try this macro:

Sub MatchData()

    Dim wshM As Worksheet

    Dim wshR As Worksheet

    Dim lngRow As Long

    Dim lngLastRow As Long

    Dim rngMatch As Range

    Set wshM = Worksheets("Master")

    Set wshR = Worksheets("Report")

    lngLastRow = wshR.Range("A" & wshR.Rows.Count).End(xlUp).Row

    For lngRow = 2 To lngLastRow

        Set rngMatch = wshM.Range("A:A").Find( _

            What:=wshR.Range("A" & lngRow).Value, _

            LookAt:=xlWhole)

        If rngMatch Is Nothing Then

            wshR.Range("B" & lngRow).Value = "NOT FOUND"

            wshR.Range("D" & lngRow).Value = "NOT FOUND"

        Else

            wshR.Range("D" & lngRow).Value = rngMatch.Offset(0, 1).Value

            wshR.Range("E" & lngRow).Value = rngMatch.Offset(0, 3).Value

        End If

    Next lngRow

End Sub

Answer 2

Thanks! It work great. I appreciated your help!