VBA to fill formula

Russk68

Active Member
Joined
May 1, 2006
Messages
466
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,873
Office Version
2013
Platform
Windows
What do you mean by Deactivate event....

but this will populate the required range

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

Russk68

Active Member
Joined
May 1, 2006
Messages
466
Hi Mike

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

Works great!

Thank you!
 

Russk68

Active Member
Joined
May 1, 2006
Messages
466
Hi Mike
How can I add another range like below but doesn't work?

Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,631
Office Version
365, 2010
Platform
Windows, Mobile
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,551
How about
Code:
Range("E7:E5000").FormulaR1C1= "=IFERROR(VLOOKUP(RC[-3], Instruments!R3C2:R40C3, 2, False), 0)"
 

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,121
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top