Using Vlookup Function In Excel Macro

[zulu1ghz]

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

 

[zulu1ghz]

OK, I fixed it, I had a space in between Sheet and 1!

aarrgghhhh, do you just love code!

thanks to anybody that takes the time to read this.

Steve

 

[PHV]

double check the values of Rolename(count) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886