Solved: Indirect Vlookup vba

[Shazam]

I?m trying to get this to work and its giving me a Run-Time error ?1004?.
Anyone knows why is that?




Sub LOOKUP()

Range("AI9").Formula = "=VLOOKUP(A9," & Range("FE9") & ",34,0)"

End Sub

[Norie]

What value is in Range("FE9")?

Whatever it is it's what's going into the formula.

[Shazam]

What value is in Range("FE9")?

Whatever it is it's what's going into the formula.

Its the file path and range.

'K:\TIME\[Staff Performance Apr-06.xls]Performance Data'!$A$9:$AH$100


but its giving me a debugger.

[mdmackillop]

Hi Shazam,
Try two apostophes at the start.
''K:\TIME\[Staff Performance Apr-06.xls]Performance Data'!$A$9:$AH$100

[Shazam]

Hi mdmackillop thanks for replying.

I tried your suggestion but it did not work. The code I piece together below seems to work. The main goal was to change the file name ??.xls dynamically while the workbook is closed.

Thanks for all the help everyone.



Range("AI9").Formula = "= VLOOKUP(RC1,K:\TIME\[Staff Performance " & Range("FE9") _
& ".xls]Performance Data'!$A$9:$AH$100,34,0)"

[mdmackillop]

Hi Shazam,
Here's my test file. Note that only one apostophe is visible in cell D1, althogh two are entered. The C:\AAA part disappears from B1 if VBA.xls is open.
Regards
MD

[Shazam]

Thanks mdmackillop your example show where I was wrong.

Once again thank You!