VBA to fill formula

[Russk68]

Hi all!

I am in need of code that will fill in the below formula in E7:E5000 with a deactivate event.
=IFERROR(INDEX(Instruments!$C$3:$C$40,MATCH(B7,Instruments!$B$3:$B$40,0)),0)

Thank you!

 

[Michael M]

What do you mean by Deactivate event....

but this will populate the required range

Range("E7:E5000").formula="=IFERROR(INDEX(Instruments!$C$3:$C$40,MATCH(B7,Instruments!$B$3:$B$40,0)),0)"

 

[Russk68]

Hi Mike

When leaving the sheet the macro will run. I was able figure that part out.

Works great!

Thank you!

 

[Russk68]

Hi Mike
How can I add another range like below but doesn't work?

Private Sub Worksheet_Deactivate()
ThisWorkbook.Sheets("Patch By Universe").Application.Calculation = xlManual
Range("E7:E5000").Formula = "=IFERROR(INDEX(Instruments!$C$3:$C$40,MATCH(B7,Instruments!$B$3:$B$40,0)),0)"
Range("F7:F5000").Formula = "=IF(E7=120,D7/120,IF(E7=208,D7/208,""))"


ThisWorkbook.Sheets("Instruments").Calculate

End Sub

 

[MARK858]

Is it erroring out and if yes is it on the line before the formulas?
You also need to double the quotes where you have ""

 

[mikerickson]

How about

Range("E7:E5000").FormulaR1C1= "=IFERROR(VLOOKUP(RC[-3], Instruments!R3C2:R40C3, 2, False), 0)"

 

[Russk68]

Double quotes fixed it.

Thanks again Mike!

 

[Michael M]

Glad WE could help....