Formula will not transfer into VBA

[Src016]

Hi All!

I am trying to put this worksheet function =IFERROR(VLOOKUP(SUBSTITUTE(E2,"/","-"),Master!$E:$E,1,FALSE),VLOOKUP(E2*1,Master!$E:$E,1,FALSE))

Into some VBA code that just drops the formula into a cell and copies it down the length of the data set. For some reason it is throwing me a 'type mismatch' error.

This is the code I am trying to drop the formula into:

Range("J1").Select ActiveCell.FormulaR1C1 = "Name"
Range("J2").Select
ActiveCell = "=IFERROR(VLOOKUP(SUBSTITUTE(E2,"/","),Master!$E:$E,1,FALSE),
VLOOKUP(E2*1,Master!$E:$E,1,FALSE))"
Selection.AutoFill Destination:=Range(Cells(2, 10), Cells(LastRowOAQ, 10)), Type:=xlFillDefault

Any ideas would be a huge help!

Thanks

 

[Michael M]

Try this

Sub MM1()
Range("J1").Value = "Name"
Range("J2:J" & LastRowOAQ).Formula = "=IFERROR(VLOOKUP(SUBSTITUTE(E2,"" / "","" - ""),Master!$E:$E,1,FALSE),VLOOKUP(E2*1,Master!$E:$E,1,FALSE))"
End Sub

 

[Src016]

Hi Michael,

I just tried the above formula in the suggested code and it gave me a compile error: Syntax error. It is highlighting the last closed parenthesis and saying expected end. Any ideas?
"=IFERROR(VLOOKUP(SUBSTITUTE(E2,"" / "","" - "")

Thank you for the help!

 

[Michael M]

Sorry if LastRowOAQ is zero, you will get an error.
It may have helped if you had posted ALL the code !!
The code provided works fine for me.

 

[Michael M]

How have you declared LastRowOAQ ?
The 2 lines between Sub and End Sub where to be copied into YOUR code, not used independantly !