PDA

View Full Version : Solved: Indirect Vlookup vba



Shazam
06-14-2006, 09:35 AM
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
06-14-2006, 09:40 AM
What value is in Range("FE9")?

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

Shazam
06-14-2006, 09:45 AM
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
06-14-2006, 11:46 AM
Hi Shazam,
Try two apostophes at the start.
''K:\TIME\[Staff Performance Apr-06.xls]Performance Data'!$A$9:$AH$100

Shazam
06-14-2006, 12:46 PM
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
06-14-2006, 01:36 PM
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
06-14-2006, 03:36 PM
Thanks mdmackillop your example show where I was wrong.

Once again thank You!