VBA Vlookup Problem

**ahwestrich** · 08-13-2007, 04:34 PM

I use the following code for a vlookup in my VBA Macro:

    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[1], Lookup!A:A,1,FALSE)), ""Not Found"", ""Found"")"
    Selection.AutoFill Destination:=Range("F2:F65000")

When this is executed in Excel, the following results show (attached picture) incorrectly. The vlookup isn't calculating?!

But then outside of the macro, when I click in the formula bar and press 'enter', it calculates it to a different result. Why isn't it calculating correctly with this code?

I'd appreciate any help, guidance, or direction someone can offer.

Thanks

**rylo** · 08-13-2007, 07:02 PM

HI

Is your calculation turned to automatic?

rylo

**ahwestrich** · 08-13-2007, 07:12 PM

Yeah, the calculations are set to that.

**rylo** · 08-13-2007, 07:27 PM

Hi

Can't see why, but the only thing I can think of is that it is getting hooked up on the R1C1 format

Try

Range("F2").Formula = "=IF(ISNA(VLOOKUP(G2,Lookup!A:A,1,FALSE)),""Not Found"",""Found"")"
  Range("F2").AutoFill Destination:=Range("F2:F65000")

Also, do you really want it to go down that far? Will take up a lot of memory.....

rylo

**ahwestrich** · 08-13-2007, 07:42 PM

i just tried that and it did not work either

You're right about the memory...this is just a test to make sure I can get this macro to work as planned.

Unfortunately this stumbling block has hindered my progress