Hi Everybody,
Hope somebody could point out what I'm doing wrong here.
I'm trying to write a Macro which uses Excel Vlookup Function.
The formula I'm assigning the cell is:-
ActiveCell.Formula = "=VLOOKUP(A3,'[" & CoreFileName & Rolename(count) & "]Sheet 1'!" & UsrLstRange & ",2,FALSE)"
I'm Using three variables here,
Corefilename holds a string value which points to a folder on my desktop.
Rolename(count)is effectively holding the name of the spreadsheet holding the data I'm looking up against. It's looping a number of times as I need to lookup against a number of different sheets.
usrlstrange is the range of the array i'm looking up against.
The code works however when I look at what the macro has placed in the cell it's as follows:-
=VLOOKUP(A107,'[c:\Documents and Settings\266558\desktop\UK Security Report\[UK SuperUser]Sheet 1]UK SuperUser]Sheet 1'!$A$3:$B$10,2,FALSE)
It fails the lookup because it seems to be specifying the filename twice over in the name of the file to lookup against.
Could anybody please point out what's wrong here?
Thanks in advance.
Steve
Hope somebody could point out what I'm doing wrong here.
I'm trying to write a Macro which uses Excel Vlookup Function.
The formula I'm assigning the cell is:-
ActiveCell.Formula = "=VLOOKUP(A3,'[" & CoreFileName & Rolename(count) & "]Sheet 1'!" & UsrLstRange & ",2,FALSE)"
I'm Using three variables here,
Corefilename holds a string value which points to a folder on my desktop.
Rolename(count)is effectively holding the name of the spreadsheet holding the data I'm looking up against. It's looping a number of times as I need to lookup against a number of different sheets.
usrlstrange is the range of the array i'm looking up against.
The code works however when I look at what the macro has placed in the cell it's as follows:-
=VLOOKUP(A107,'[c:\Documents and Settings\266558\desktop\UK Security Report\[UK SuperUser]Sheet 1]UK SuperUser]Sheet 1'!$A$3:$B$10,2,FALSE)
It fails the lookup because it seems to be specifying the filename twice over in the name of the file to lookup against.
Could anybody please point out what's wrong here?
Thanks in advance.
Steve