Code to work like a Vlookup with a dynamic range

[chrisellis250]

Hello all,

In Sheet2 I have a range of data, that can range from 2 to 200000.

I have a table on Sheet8. I am matching a number in B4 on Sheet8 to a number in Column E or G (it could be in either) in Sheet2.

I am then looking to pull in data for the 1st instance that this matched number appears in E or G and then pull in the following data:

-Sheet2 column B into Sheet8 I4
-Sheet2 column C into Sheet8J4


Then similarly for the last instance that this matched number appears in E or 6 and then pull in the following:

-Sheet2 column B into Sheet8 k4
-Sheet2 column C into Sheet8 L4

Any ideas?


Many thanks,

[Marc L]

Hi !

Use CurrentRegion property for example, as you can see in VBA inner help …

[chrisellis250]

I don't understand what you mean, Im afraid...