Range for Vlookup: refer to columns stored in a variable

[stefy1984]

Hi,
I’m new in the VBA world and in this forum. I’m trying to do a vlookup but I’m facing a problem
I need to refer to a range in another worksheet. The columns to which I want to refer can vary position. Therefore I get their column number and store it in a variable (ColC, ColN)
I don’t know how to refer to the range. If I refer to the range by the letter of the columns, the vlookup works.

Please Login or Register  to view this content.

However these columns can move and I need to find a way to refer to this range without using (“B:E”).
I’ve tried this but it doesn’t work

Please Login or Register  to view this content.

Any suggestion? Thanks a lot for the help!

[Xer2]

Try creating a string variable and concatenating the two other variables, then using the created string in the VLookUp:

Please Login or Register  to view this content.

[stefy1984]

Hi,
thanks for the help. It's still not working.
Any other suggestion?

[Xer2]

Can you submit a sample of the workbook with fake data so I can manipulate it?

[stefy1984]

Hi,
this is an example. Thanks

[Xer2]

Try this:

Please Login or Register  to view this content.

[stefy1984]

Thanks for the help,
but what I'm trying to achieve is not to use explicitly columns "B:E" in the Vlookup Range.
These columns can change and this is why I'm storing the colum number in the variable ColC and ColN.

you wrote this, but in thi B and E are used

Please Login or Register  to view this content.

I'm tring to define the range for the Vlookup by using ColC and ColN; but if I write this it will not work

Please Login or Register  to view this content.

I've also tried

Please Login or Register  to view this content.

[Xer2]

Try the code in this. It is long and not good looking, but it works.

[stefy1984]

Thanks a lot for the help.

I also kept trying and I think I found my mistake.
This seems to work

Please Login or Register  to view this content.

[Xer2]

Oh yeah, looks much better!