CODEWITHSUNDEEP
excelvbaforeachindexingmatch
Harley B
Harley B

Reputation: 569

Changing the Reference in the formula For Each Cell in a loop

I have a subroutine to apply an Index/Match to a range of 7 cells, looking up against a named range with a different Index value applied to the formula in each cell.

I have a version of it that works, but I'm trying to trim my code down so I don't have a long and unwieldy sub. I'm sure there's a way to get it to work in a For Each Cell loop, but for the life of me I can't figure out how. I could probably figure it out using a vlookup, but I believe an Index/Match is a better solution when looking up against large ranges of data (my named table is over 10k rowslong)

Here's what I have that actually works but is a bit long:

    Range("C38:F38").Select
    Selection.MergeCells = True
    Selection.Formula = ("=IFERROR(INDEX(MAIN_DATA[NAME],MATCH($D$10,MAIN_DATA[PURCHASE_ORDER_ID],FALSE),0),"""")")
Range("C39:F39").Select
    Selection.MergeCells = True
    Selection.Formula = ("=IFERROR(INDEX(MAIN_DATA[infodyn_DELIVERY_ADDRESS.ADDRESS_1],MATCH($D$10,MAIN_DATA[PURCHASE_ORDER_ID],FALSE),0),"""")")
Range("C40:F40").Select
    Selection.MergeCells = True
    Selection.Formula = ("=IFERROR(INDEX(MAIN_DATA[infodyn_DELIVERY_ADDRESS.ADDRESS_2],MATCH($D$10,MAIN_DATA[PURCHASE_ORDER_ID],FALSE),0),"""")")
Range("C41:F41").Select
    Selection.MergeCells = True
    Selection.Formula = ("=IFERROR(INDEX(MAIN_DATA[infodyn_DELIVERY_ADDRESS.ADDRESS_3],MATCH($D$10,MAIN_DATA[PURCHASE_ORDER_ID],FALSE),0),"""")")
Range("C42:F42").Select
    Selection.MergeCells = True
    Selection.Formula = ("=IFERROR(INDEX(MAIN_DATA[infodyn_DELIVERY_ADDRESS.TOWN],MATCH($D$10,MAIN_DATA[PURCHASE_ORDER_ID],FALSE),0),"""")")
Range("C43:F43").Select
    Selection.MergeCells = True
    Selection.Formula = ("=IFERROR(INDEX(MAIN_DATA[infodyn_DELIVERY_ADDRESS.COUNTY],MATCH($D$10,MAIN_DATA[PURCHASE_ORDER_ID],FALSE),0),"""")")
Range("C44:F44").Select
    Selection.MergeCells = True
    Selection.Formula = ("=IFERROR(INDEX(MAIN_DATA[infodyn_DELIVERY_ADDRESS.POST_CODE],MATCH($D$10,MAIN_DATA[PURCHASE_ORDER_ID],FALSE),0),"""")")

And here I've figured out how to apply the loop to deal with the merging part, but I'm stumped on how to do the Index/Match changing just the column headers on the Index in each instance:

For Each Cell In Range("C38:C44").Cells
    Cell.Resize(, 4).Select
    Selection.Merge
    'Something in here with the Index/Match Formula changing the Column Header on each turn through the loop
Next Cell

Any thoughts or advice very gratefully appreciated.

Upvotes: 0

Views: 117

Answers (1)

Bmo
Bmo

Reputation: 1212

You can set up an array for the column names and concatenate them in the loop. You just need to keep track of the column.

Dim columnNames(6) As String
Dim i as Integer
i=0


columnNames(0) = "NAME"
'Rest of columns.
columnNames(6) = "infodyn_DELIVERY_ADDRESS.POST_CODE"

'Inside loop
 Selection.Formula = ("=IFERROR(INDEX(MAIN_DATA["&  columnNames(i) &"],MATCH($D$10,MAIN_DATA[PURCHASE_ORDER_ID],FALSE),0),"""")")
i = i +1

Upvotes: 1

Related Questions