Search multiple worksheets in multiple workbook and return value beside matching cell

[FordBiggs]

Hi Guys,

I'm starting to get the hang of VBA but I can't seem to make this work. Currently, I have this working using the excel formula but it's very limited (both is the length of code and limited to search only in the same workbook). I'm currently using this:

Takes the value of A10 and searches through multiple sheets and returns adjacent value.

=IF(ISBLANK(A10),"-",VLOOKUP(A10,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2"}&"'!A1:A350"),A10)>0),0))&"'!A1:B100"),2,0))

But I need to upgrade this to VBA, basically I need it to do:

- I have a separate Worksheet just for the searching. Input something in Column A of said sheet, value of Cell will be searched in multiple other worksheets (or if possible, multiple workbooks) for a match in Range A1-A500 of worksheet.
- Once match is done - it takes the value of adjacent cell (column B) and returns to the original "searching" worksheet on an adjacent cell as well (column B).

I have this code from JoeMo which I'm trying to modify but still missing the actual searching codes.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A10:A500")) Is Nothing Then
    For Each C In Intersect(Target, Range("A10:A500"))
        If Not IsEmpty(C) Then
            ' Search code here
            C.Offset(0, 1).Value = "Return the value from adjacent cell of the search"
        Else
            C.Offset(0, 1).Value = "-"
        End If
    Next C
End If
End Sub

Would appreciate some help or at least a push in the right direction!

Thanks very much guys!

 

[FordBiggs]

Hi guys. Just bumping my thread from last week. Any help would be greatly appreciated!