How to set up a vlookup or macro so that the values follow the date-CodePudding

I have a table where I want the values to follow the date. The dates are shifted with the macro and the dates have a vlookup set for the date. However, when my dates shift, the values don't follow the date, and I have something like this:

my code:

wbMe.Worksheets("input_6").Range("X26").Delete xlShiftToLeft
wbMe.Worksheets("input_6").Range("AJ26").Value = DateAdd("m", 1, Range("AI26").Value)

CodePudding user response：

You can use the formula INDIRECT(ADDRESS(2,CELL("col",D3))) to point to the date row. Where D3 is each VLOOKUP cells own address. So each cell would put its own address into that formula, and the formula returns the date of its own column.

This formula will create a reference that always points to row 2 of the cells current column.

A normal VLOOKUP Formula: =VLOOKUP(P2,A1:F11,2,FALSE)

Modified to use Indirect: =VLOOKUP(INDIRECT(ADDRESS(2,CELL("col",P3))),A1:F11,2,FALSE)

Here is a breakdown of what that formula is doing:

CELL("col",P3) : Returns the column value of the given address (Column P = 16)

ADDRESS(2,16) : Returns the address of a cell as a string, given the row & column values (Row 2 Column 16 => "$P$2")

INDIRECT("$P$2") : Takes an address as a string, and gives the value in the cell at that address.