VBA Vlookup Problem

[ahwestrich]

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

Please Login or Register  to view this content.

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]

HI

Is your calculation turned to automatic?


rylo

[ahwestrich]

Yeah, the calculations are set to that.

[rylo]

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

Please Login or Register  to view this content.

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


rylo

[ahwestrich]

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

[rylo]

Hi

What reference style do you have in your sheet? R1C1, or non R1C1?


rylo

[ahwestrich]

I used the R1C1

[rylo]

Hi

Try changing it to the non R1C1 and run the code. If it calculates properly that way, then add the conversion to the start of the code, then turn it back again at the end. Record a macro to get the code to do those steps.

If that doesn't work, then let us know...

rylo

[ahwestrich]

I've tried using both R1C1 and non and both didn't work. Recording the macro hasn't worked either because it gives me that same code. Is there code for the cursor placed in the formula bar then to press enter. That is what I have to do in order for it to calculate correctly.

[rylo]

Hi

If the workbook isn't too large, then try dragging each of the sheets to a new workbook, save it and see if that will work with the R1C1 set to the A1 format.

If that doesn't work, then the only thing I can think of is to go down the column and make each of the formulas equal to the formula. This should have to same effect as editing, but (a) you shouldn't need to do it and (b) it will take some time.

Any chance of putting up the workbook, or an example file that is doing the same thing???

rylo

[ahwestrich]

Hey Rylo,
Thanks for putting in the time to try and solve this problem for me. I'm working with some sensitive data that I'm probably not supposed to the internet so I changed it around a bit. Here's the perfect example of what's happening for me in this macro. This vlookup was generating from the code above. Column F evaluates to Found when if you look at the second worksheet, "Z", is clearly not found. But if you take each cell individually (i.e. G2), put the cursor in the formula bar, and press enter, it calculates it! Why wouldn't it calculate it the first time?

[rylo]

Hi

I modified the code so that it will only go down to the last entry in column G.

This shouldn't make any difference to anything.

Make sure you are using the A1 reference style, open the file, and run the macro. Works OK for me.


rylo

[ahwestrich]

Alright it worked! But what the heck!? For lessons learned, besides the efficiency factor, what is the difference between:

Please Login or Register  to view this content.

and the working version:

Please Login or Register  to view this content.

I appreciate your help Rylo!

[rylo]

Hi

I think I may have stumbled across something. When I changed to the R1C1 format, and put the formula in manually, the section lookup!A:A became Lookup!c[-5] (d'oh - silly me!!!)

So when I changed the code to

Please Login or Register  to view this content.

it worked. Best I can think of is that it was treating A:A as a range name, and erroring out as there was no range name!

See if you can get it working in R1C1 mode with the above code.


rylo