0

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.

Improve this question
2
  • why don't you just copy the formula down? That's possible with VBA, too! Commented May 22, 2017 at 3:25
  • 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 Commented May 22, 2017 at 5:30

1 Answer 1

Reset to default
0

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))),"")
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.