Code for vlookup with changing ranges

**trier** · 03-23-2012, 05:13 PM

Hi, I have been unable to find a vba code for vlookup that works for me. Would someone be able to help please. I would prefer a vba code solution if possible.

As a function in a cell it looks like this: =VLOOKUP(A1,G1:K5,4,FALSE) (although G1:K5 is just an example it could go to K5000)
I need it to work through all the rows of col A where the number of rows in col A will vary for each time the macro is run with new data.
Cols G to K will all have the same number of rows as each other but this number will will vary for each time the macro is run with new data.

thank you.

**DGagnon** · 03-23-2012, 05:17 PM

you could reference the entire column like this, it would work regardless of how large the column is.

=VLOOKUP(A1,G:K,4,FALSE)

**trier** · 03-23-2012, 05:40 PM

Originally Posted by DGagnon

you could reference the entire column like this, it would work regardless of how large the column is.

=VLOOKUP(A1,G:K,4,FALSE)

Thanks DGagnon but that I'm hoping for the full vba code because when I've tried writing some myself (with all hope and no knowledge) and use something along the lines of "application.worksheetfunctions.vlookup (A1,G:K, ..)" I get messages saying the colon in G:K is not acceptable.

**DGagnon** · 03-23-2012, 05:42 PM

do you want the vlookup to be in your cell, or are you trying to get the value that woudl be the result of the vlookup?

could you also provide the surounding code?

**trier** · 03-23-2012, 11:00 PM

Originally Posted by DGagnon

do you want the vlookup to be in your cell, or are you trying to get the value that woudl be the result of the vlookup?

could you also provide the surounding code?

I want the value to be in the cell. I forgot to mention that these values will be put into col F. At this stage there is no other code - this is the first process done to this data. When the vlookup results are in place I will then be comparing the values in F with those in D (in the same row) . I hope that is a bit clearer. Thanks

**DGagnon** · 03-23-2012, 11:19 PM

you will need to include this in a loop im assuming, try something like this.

Please Login or Register  to view this content.

where i is your current row in your loop

**trier** · 03-24-2012, 04:31 PM

Originally Posted by DGagnon

you will need to include this in a loop im assuming, try something like this.

Please Login or Register  to view this content.

where i is your current row in your loop

I'm embarrassed to say I can't get this running as I'm only new to vba. Would you please provide the entire code for running this as if you are writing it to a new work book and this is the only function that will be run.