How to use variable on VLOOKUP-table_array

[ExcelPupper]

Is it possible to use a variable on table_array for a Vlookup formula on vba?
Im trying to run my code below but it gives error.

Sub LookUpData()
'
'
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
   
    Application.ScreenUpdating = False
    Dim MyFile As String, MyWB As String, ws As Worksheet

    MyLoc = "C:\Users\ADMIN\Desktop\Test\vba\"
    MyWB = MyFile & "SourceData.xlsm"
   
   
  
    Set ws = ThisWorkbook.Sheets("Data")
   
    ws.Range("C1").Formula = "=VLOOKUP($A1,'MyWB'!$B:$D,3,FALSE)"
   
   
End Sub

 

[jasonb75]

Yes it is, with the name as a string you need to use

"=VLOOKUP($A1,'" & MyWB & "'!$B:$D,3,FALSE)"

 

[ExcelPupper]

Yes it is, with the name as a string you need to use

"=VLOOKUP($A1,'" & MyWB & "'!$B:$D,3,FALSE)"

It works on my trial file but when I tried it to the actual data I use, it takes time to execute. Maybe because the source data for my table_array is big? (it's around 60MB and currently it has 640K + rows of data)
Do you have other suggestion that would speed up this macro?

 

[jasonb75]

With that volume of data anything is going to be slow. How many rows is the formula being added to?

 

[ExcelPupper]

With that volume of data anything is going to be slow. How many rows is the formula being added to?

It varies. But maximum number of rows I've encountered before was around 400rows and 3 columns.

 

[jasonb75]

If you're not already doing so I would suggest setting calculation to manual and screen updating to false while the code runs, that should speed it up a bit but with ~1200 formulas looking at 640k rows of data I would expect a significant wait for it to complete.

There may be other ways that will work faster, but not ones that I am familiar with.

 

[ExcelPupper]

Also Im having problem on what sheet the table_array should look up to because I only declared the FileName on this line:

ws.Range("C1").Formula = "= VLOOKUP($A1,'" & MyWB & "'!$B:$D,3,FALSE) "

 

[jasonb75]

Just append it to the file name, e.g. with the sheet name declared as a text string and assigned to MyWS

ws.Range("C1").Formula = "= VLOOKUP($A1,'[" & MyWB &"]" & MyWS & "'!$B:$D,3,FALSE)"