Loop through range in column B and setting index-match formula in column E based on value in column B

Question

Newbie with VBA here. I need help in setting up a VBA command that will loop through range in column B and sets an index-match formula in column E based on value in column B.

I tried editing commands from other posts but I can't seem to find out how it works, based on this link Excel VBA - Loop through range and set formula in each cell

The index-match formula I need basically matches the ID numbers with names based on a database in another sheet. I currently using fixed index-match functions in the sheet but this screws up filter/sorting of entries. I've used another macro that clears contents of cells the return blanks - but it takes ages to run through the sheet.

Heres the formula I need in a cell in column E if cell in column B is between 1 and 450.

=IF($B49="","",INDEX(NAMES,MATCH($B49,ID,0)))

Thanks in advance.

why don't you just copy the formula down? That's possible with VBA, too! — teylyn
Thanks, but i go back to square one - which i get cells that return blanks that screws filtering/sorting. i need it to place the formula when Idnumbers are present and skip blanks so that i dont get cells with formulas that return blank values — Raeon Jakow Laspiñas

NHW NHW · Accepted Answer · 2017-05-22T06:04:11

It sounds like a simple solution might be:

For Each cell In Range("B2:B2000")
If cell.Value <= 450 And cell.Value >= 0 Then
    i = cell.Row
    Range("E" & i).Formula = "=IF($B" & i & "="""","""",INDEX(NAMES,MATCH($B" & i & ",ID,0)))"
End If
Next

which will set the formula in Col E for every value it finds in Col B between 1 and 450.

This might become a bit slow for long lists. You could turn off auto calculations and sheet events to speed things up a bit with:

' Deactivate - put this at the start of your code
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

' Reactivate - put this at the end of your code
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Which on my PC gave the following speed boost (average speed after 20 iterations):

Without deactivation: 1.043359375 s per iteration
With deactivation: 0.270703125 s per iteration

Alternatively, if it is the error from the index function that is throwing filters and sorting off you could try wrapping the function in a IFERROR() function and bypass VBA altogether.

=IFERROR(IF($B49="","",INDEX(NAMES,MATCH($B49,ID,0))),"")

Loop through range in column B and setting index-match formula in column E based on value in column B

1 Answers