VBA Vlookup to cycle through open books and select correct one

[mladenjevtic]

Hello All,
I apologize about the title of this post, it may not be the most fortunate. I have searched through the web, but i can't seem to figure out result of my problem.

What i'm trying to do is next:
Use vlookup in one file and pull data from the other file. Sounds simple enough, but for VBA beginner as I am - not that simple
It's easy to do it with vlookup, but filenames are always different and i want to make this repetitive task automatic. What makes the files unique is that they have certain words always in the filename:
File #1 (where i type the vlookup) has wording in the filename "AR info". File #2 (data file from which i'm looking the info) has words in the filename "File Master".

What i found so far, i can activate those files by looping through open workbooks, but i don't know how to implement this to do a vlookup and cycle through open workbooks using example below:

Sub Test()
Dim wb as Workbook

For Each wb In Workbooks        
If InStr(1, wb.Name, "File Master", 1) > 0 Then
            wb.Activate
            Exit For
        End If
    Next wb
End Sub

I'm trying to do this in range "N2" of file #1 .
=VLOOKUP($G2,'[File Master.xlsx]Ready'!$B:$BH,5,0)
I have copied only the one formula, but i have a range of them.

I appreciate any given help!
Thank you!

 

[offthelip]

I would do it without using Vlookup but using varinat arrays instead which is much more flexible and faster , se this thread:
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[mladenjevtic]

@offthelip
Thank you, i have checked it, but it's way above my knowledge of vba.

As i understood, it does pretty heavy job in matter of seconds, but it's only looking in one file with "sheet1" and "sheet2".

What i'm stumbled upon is how to adjust it to my scenario here, since i'm looking in two different files.

 

[offthelip]

something like this:
I have limited the range to pickup by checking for the last row with anything in it in column B
I am not sure hwere you want the result so iI am putting it out in message box.
You haven't specified which sheet the variable g2 is on so I assumed it is the active sheet
The advantage of doing it this way is you can do multiple search in the same loop

Sub Test()
Dim wb As Workbook
currentwb = ActiveWorkbook.Name
For Each wb In Workbooks
If InStr(1, wb.Name, "File Master", 1) > 0 Then
            wb.Activate
            Exit For
        End If
    Next wb
Worksheets("Ready").Select
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
datar = Range(Cells(1, 2), Cells(lastrow, 60))  ' 60 is column BH
Workbooks(currentwb).Activate
srch = Range("G2")
For i = 1 To lastrow
 If srch = datar(i, 1) Then
  ' we have found it
   ' I am not sure where you want the result so :
    tt = datar(i, 5)
    MsgBox (tt)
 End If
Next i


End Sub

 

[mladenjevtic]

Thanks for your help. Sorry about a delayed response, i was sick for few days.

So i think i get it how it works.
I need data to show in Active Workbook, which is the file called "AR info", starting from column N, on "sheet1".
How would i import data for whole range in "AR info" file? Would that be the part where you wrote in MsgBox? Since it seems like for now, it checks only what's located in one cell "G2", not the whole range of cells.

 

[offthelip]

You analysis is correct , your question wasn't clear about exactly what you wanted. (It still isn't, but at least a bit clearer)
I have added a loop to do the macth for every item in column G and write the results into column N

Sub Test()
Dim wb As Workbook
currentwb = ActiveWorkbook.Name
For Each wb In Workbooks
If InStr(1, wb.Name, "File Master", 1) > 0 Then
            wb.Activate
            Exit For
        End If
    Next wb
Worksheets("Ready").Select
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
datar = Range(Cells(1, 2), Cells(lastrow, 60))  ' 60 is column BH
Workbooks(currentwb).Activate
Worksheets("Sheet1").Select
lastgrow = Cells(Rows.Count, "G").End(xlUp).Row
gdata = Range(Cells(1, 7), Cells(lastgrow, 7))
For j = 2 To lastgrow
srch = gdata(j, 1)
For i = 1 To lastrow
 If srch = datar(i, 1) Then
  ' we have found it
   ' I am not sure where you want the result so :
    tt = datar(i, 5)
    Cells(j, 14) = tt
    Exit For
 End If
Next i
Next j






End Sub

 

[mladenjevtic]

Well, that's it actually. Results should show starting in cell "N2". You nailed it to the bone

It's working flawlessly

I appreciate your time and help for helping me on this!
I understood how the code works completely. Awesome!