VLookup VBA error trapping

[Xrull]

Can anyone tell me how to modify this formula to trap errors? I tried wrapping it in IsError (code) Then...but it doesn't work. I don't want a function.

Please Login or Register  to view this content.

I'm trying to modify this formula :

Please Login or Register  to view this content.

The column search changes every month based on the information in column F.

Thanks,
Xrull

[rylo]

Hi

Not sure what you mean by not wanting a function, but one way to get around this is to use COUNTIF (even though it IS a function) to get a count of the item that is the lookup value. If the result is 0, then there is nothing there, so don't do the lookup. Otherwise go ahead with the lookup.

rylo

[Xrull]

rylo,
I don't need a UDF. I need a Sub procedure. If you run the code, it leaves no formulas in the cells. I'm trying to avoid leaving formulas in the cells because the formulas take up a lot of memory, and the sheet takes too long to calculate when I run the formulas for sheets over 40000 rows.
Thanks for helping me clarify the type solution I require.
Xrull

[broro183]

hi

You may need to do some fine tuning but try this:

Please Login or Register  to view this content.

I have used the LastRow technique to minimise the referenced range which may help with calculation speed.


hth
Rob

[rylo]

Hi

another way

Please Login or Register  to view this content.

rylo