Defining a variable as Vlookup

I've never seen WorksheetFunction in recorded code.

What exactly did you record?

Have you taken code like this and changed it?

Please Login or Register  to view this content.

Like I said I've never seen the macro recorder produce code that includes WorksheetFunction.

To use WorksheetFunction.VLookUp you need ranges for the lookup value and the lookup range.

In what you've posted, RC2 means the cell in column 2 (B) ont the same row the formula is going in.

You aren't putting the formula in a cell so it's hard to know what to replace RC2 with.

The lookup range is easier it would be Worksheets("Accountability").Range("B:C").

I'm not sure what you mean, you can't do it without ranges.

The code you posted is almose how it should be done - Accountablity should be Worksheets("Accountability"), as I posted, and it should be separated from Range by a dot.

I doubt the macro recorder would have recorded this:

Please Login or Register  to view this content.

The code would look something like this:

Please Login or Register  to view this content.

Change Range("A2") to whatever you need as the lookup cell.


Regards, TMS

If I put a VLOOKUP formula into cell B2 and then record refreshing that formula, I get:

Please Login or Register  to view this content.

So, in this instance, the formula was: =VLOOKUP(A2,Accountability!C2:C3,2,FALSE)

But I could just as easily put the formula in directly

Please Login or Register  to view this content.

And I don't need the Select. I could use:

Please Login or Register  to view this content.

But, if you're trying to get the value returned by the VLOOKUP in a variable, maybe this is what you want:

Please Login or Register  to view this content.

Obviously, what you get in terms of relative references depend on which cell you put the formula into.


Regards, TMS

Can you post the exact code you are using?

There might be nothing wrong with the code, the problem could be that the VLookup is actually failing.

Have you tried testing the VLookup manually on a worksheet?

By the way, if you use C2:C3 the formula will fail as it's only one column (2 cells actually) and the formula is looking to return the 2nd column of the lookup range.

C2:C3, in R1C1 notation, translates as B:C in A1 notation.

@Norie: bit of a crock of .... really. Sorry, didn't dawn on me that was only one column (two cells) though it did occur to me that the search was pretty limited.


@jipstastic: I think you would save yourself and us a lot of time if you uploaded a sample workbook and explained what the formula is you are trying to evaluate and what you are trying to do with it.

Regards, TMS