Changing vlookup value in VB

[benno87]

Hi All,

I have the below formula.

Please Login or Register  to view this content.

I need to change the lookup value "G3" relative to the cell being executed. Therefore if the vlookup is on cell K5, I want the lookup value to be G5. K6, the lookup value is G6, etc.

Any help is greatly appreciated.

Benno

[Kenneth Hobson]

Change Range("G3") to:

Please Login or Register  to view this content.

[benno87]

Hi Kenneth,

This partially works but I get an error.
"Unable to get the Vlookup property of the WorksheetFunction class"

Also, if neither of these work, I want the text to remain black. Should I enter this code below the line "Cell.Font.Color = vbBlue"?

Please Login or Register  to view this content.

I appreciate your help. I've been trying to work this out for a week so I'm stoked you're helping.

Benno

[Kenneth Hobson]

I don't have your data to check. If you have a short example xls, attaching it would let us help more directly. It gives us something to test. Some worksheet functions take a bit of tweaking to Evaluate in VBA.

Yes, I would reset the font color as one run might set it to something else.

If this is not a one time need, conditionally formatting should work as well. If it is something to process once and then change on the fly as cells are modified, you can use conditional formatting or a worksheet Change event. In this way, it would only update those cells that change.

[benno87]

Hi,

Actually this is working but when it gets to a blank cell (I used 500 lines as the number of lines is always changing but will never go past 500, but there will be blank lines at the end) the error is appearing.

How do I get the sub to end when it encounters this error?

Thanks again

[Kenneth Hobson]

You can use:

Please Login or Register  to view this content.

Of course it might be better loop through a range based on data that exists. You can then use something like:

Please Login or Register  to view this content.

You can use, On Error Goto TheEnd, sort of thing if you like.

[benno87]

Hi Kenneth,

Thank you very much. With your help, I modified the code a bit to get it to work how I wanted and it's now doing precisely what I wanted it to. Your help has been invaluable as you've just made my life easier. FYI, the code I have now is below.

Please Login or Register  to view this content.

Thanks again for your help.

Benno