VLOOKUP and Variable Workbook using VBA

[msquared99]

I'm a bit bumfuzzled here with a macro using VLOOKUP. I am making the formula actually look up a value to the left. Maybe the FIND method would be better?

Workbook1 is opened by the macro and given the name "FileName" because the name is variable.

Workbook2 is run from a database and it's name is "Red Flag Report", its name never changes and is the active workbook at this time.

Here is the formula as I invision it:

Worksheets("Sheet1").Range("E3").Formula = "=vlookup(d4,choose({2,1}," & Filename.Sheet1.range("$D$3:$D & lastRow1")"& , &"" & Filename.sheet1.range("$h$3:$h & lastRow1")",2,0)"

What would the proper VBA syntax be?

Thanks!

 

[msquared99]

I'm pretty sure my issue is with this part:

lastRow1")"& , &"" & Filename