Formula works but Run Time Error 13 Type Mismatch in VBA

[uberathlete]

Hi everyone. This is driving me bananas. Basically, my macro is supposed to enter a formula and copy it down.

    
Sub Parts_Usage()

With Sheets("Parts Usage")
        .Range("C2:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(A2,'Parts Prices'!$A:$H,8,FALSE),VLOOKUP(" * "&LEFT(A2,8)&" * ",'Updated Parts Prices'!$A$11:$Q$236,13,FALSE))"
    End With

End Sub

The formula will lookup cell A2 (a part number) from the Parts Prices sheet and return the price, but if there is an error, then lookup the first 8 digits of cell A2 from the Updated Parts Prices sheet.

The formula works fine on its own but when I enter it as VBA I get a runtime error 13 Type Mismatch. I would really appreciate any help on this. I have attached a workbook.

[martindwilson]

try

Sub Parts_Usage()

With Sheets("Parts Usage")
        .Range("C2:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(A2,'Parts Prices'!$A:$H,8,FALSE),VLOOKUP("" * ""&LEFT(A2,8)&"" * "",'Updated Parts Prices'!$A$11:$Q$236,13,FALSE))"
    End With

End Sub

you need to double up the quotes in the formula

[TMS]

Maybe:

Sub Parts_Usage()
'
'Enter a vlookup formula which determines the price of each part
With Sheets("Parts Usage")
    .Range("C2:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = _
        "=IFERROR(VLOOKUP(A2,'Parts Prices'!$A:$H,8,FALSE),IFERROR(VLOOKUP("" * ""&LEFT(A2,8)&"" * "",'Updated Parts Prices'!$A$11:$Q$236,13,FALSE),""""))"
End With

End Sub

As Martin says, double up on the quotes ... but you need a second IFERROR to cope with the failure of the second VLOOKUP.

Regards, TMS

[uberathlete]

Thank you for your replies TMShucks and Martin. I tried the double quotes but the formula doesn't seem to work. It returns an error, for some part numbers. So I guess it's not executing the second vlookup in the formula.

[martindwilson]

Sub Parts_Usage()

With Sheets("Parts Usage")
        .Range("C2:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(A2,'Parts Prices'!$A:$H,8,FALSE),VLOOKUP(""*""&LEFT(A2,8)&""*"",'Updated Parts Prices'!$A$11:$Q$236,13,FALSE))"
    End With

End Sub

there was a space either side of the * which i take it you didn't mean to be there

[uberathlete]

Ah yes you're right. Works perfectly now. Thank you so much!!

[martindwilson]

the code wont do that, once the formula is in the cell that's it, check the data being looked up