VLOOKUP Macro must be run twice

[dhd2005]

Hello,

I have a macro whose primary task is to add VLOOKUP formulas to Columns E and F.

When the macro is run for the first time, the VLOOKUP returns #N/A, but if I run it again without making any modifications, the VLOOKUP function works perfectly.

The code is as follows:

Please Login or Register  to view this content.

Additionally, when I stop the code at the "Set Rng" line on the first run, it returns the correct VLOOKUP value.

Does anyone know why the macro would have to be run multiple times to keep the formula from returning a #N/A value?

Thanks in advance,
David

[MarvinP]

This sounds like a timing problem to me. It may be because the url has not completely opened to do the vlookup. Read http://en.allexperts.com/q/Visual-Ba...s-Function.htm

I'd try to do a DoEvents after the first vlookup and see what happens. If that doesn't do it, is there a way to open the url path before doing the vlookup?

[dhd2005]

Hi Marvin,

Thank you for the quick response and great suggestion! So this does help - but only if that part of the code stands alone. For example, the following code works:

Please Login or Register  to view this content.

However, when I add any actions after that (example below), it doesn't work. For example:

Please Login or Register  to view this content.

On the second code, the values are come back as #N/A.

I'd prefer to not have to open the spreadsheet - it's just one more step for the end user. Do I need to add additional DoEvent instances throughout the code? Any recommendations?

Thanks again for your help!