Hello all,

I received quite a bit of help last week on a macro and I am to another stopping point for myself. I was able to write enough of the macro to get a decent start but I cannot seem to make the macro loop and list all of the cells that match the searched cell. Right now it only lists the first match, then moves on. I would rather have it list all of the matching cells and continue until the end of the list, then move to the next searchable term.

Here is my macro thus far.


Sub COMPARE()
    Dim wbk As Workbook
    strFirstFile = "c:\Documents and Settings\mjbauer\Desktop\DocAttributes.xlsm"
    strSecondFile = "c:\Documents and Settings\mjbauer\Desktop\Marietta_Facility_Export.xlsm"
    strThirdFile = "c:\Documents and Settings\mjbauer\Desktop\Compare_TEST.xlsm"
    Set wbk = Workbooks.Open(strFirstFile)
    With wbk.Sheets("DocEntry")
        .Range("Z2:Z10000").Copy
    End With
    Set wbk = Workbooks.Open(strThirdFile)
    With wbk.Sheets("Sheet1")
        .Range("A2:A10000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End With
        
    ActiveWorkbook.Close SaveChanges:=True
    Set wbk = Workbooks.Open(strSecondFile)
    With wbk.Sheets("Export Worksheet")
        .Range("C2:C10000").Copy
    End With
    Set wbk = Workbooks.Open(strThirdFile)
    With wbk.Sheets("Sheet1")
        .Range("B2:B1000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End With

    With wbk.Sheets("Sheet1")
        .Range("C2").FormulaR1C1 = "=IF(B2>0,IFERROR(ADDRESS(MATCH(B2,$A$1:$A$23,0),1,4,TRUE),"NOT FOUND"),"")"
        .Range("C2").AutoFill Destination:=Range("C2:C10000"), Type:=xlFillDefault
    End With


End Sub

So as some background on the macro. It is taking two lists in two separate excel workbooks and combining the columns that should contain similar information. After it puts the matchable columns side by side it begins to match the entry in B1 to the entire A column. I have it to the point that it will list the first match it finds (lists it in Column C in the same row) but it will not list multiples.

Any thoughts on how to get it to list all of the matches as opposed to just one? Any help would be greatly appreciated.

Thank you all in advance, and if you would like me to build up dummy files to show exactly what I am looking for I would be glad to, I just didn't want to give a ton of information on here if it might just be a simple code change. Not sure as I am not a programming expert by any means. I actually know very little lol.


Mike