CODEWITHSUNDEEP
vbavlookup
Mike
Mike

Reputation: 11

Is there a way to change a vlookup table_array in VBA?

Basically, I am trying to use VBA to change the VLookup table_array from one tab to another. Currently the VLookup value is something like this

=+vLookup($A4,'02.19.21'!$A$1:$O$9000,F$1,False)

02.19.21 is the name of a tab the data is pulling from, but every week a new tab is created with the most recent data, so the next week the data will need to pull from a new tab that will get created called "02.26.21"

I need vba to change '02.19.21' to '02.26.21', which is where the new data will be pulled from

Edit, I have tried doing a macro recorder, but the issue is that the data is changing weekly, meaining, if I record and change the date to 02.26.21, then when i need to do it for the next week, march 5th, it would not return that data. I tried the date function in VBA and then used it as a string, since the vlookup would be looking at a tab that is called 02.26.21, but got errors when I did that.

Upvotes: 0

Views: 245

Answers (1)

Tim Williams
Tim Williams

Reputation: 166550

I would consider using a named range for your vlookups instead of a direct sheet+range reference.

See here for named ranges for example: https://www.contextures.com/xlnames01.html

So your vlookup goes from this:

=+vLookup($A4,'02.19.21'!$A$1:$O$9000,F$1,False)

to this:

=+vLookup($A4,LookupTable,F$1,False)

So then when your code needs to change the source sheet, you can do something like:

ThisWorkbook.Names("LookupTable").RefersTo = _
           ThisWorkbook.Sheets("02.26.21").Range("A1:O9000")

Upvotes: 0

Related Questions