Run-time error 13: Type Mismatch Error

[VaYooper]

I have this mismatch error occurring in the ActiveCell.Formula section that has been driving me crazy trying to figure out. It's the same one I use for all my various macro sheets but for osme reason this one is giving me problems. The cell is formatted as General and so is the data being pulled in. Am I missing something obvious? Thanks

    Dim LastRow As String
   
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
ActiveCell.FormulaR1C1 = _
    Range("N2:N" & LastRow).Formula = "=IF(ISERROR(VLOOKUP(RC[-5],Codes!C[-13]:C[-12],2,FALSE)),"" "",VLOOKUP(RC[-5],Codes!C[-13]:C[-12],2,FALSE))"[/B][/COLOR]
       
    Columns("N:N").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Fluff]

For that formula you need to use .FormulaR1C1, you also need to double=up on all the quotes inside the formula.

 

[Joe4]

Just a follow-up to what Fluff said, if you have trouble coming up exactly what the formula should look like, simply turn on your Macro Recorder and record yourself entering the formula into one of these cells. Then you can stop the Macro Recorder, and copy/paste the formula section of what you recorded to your code.

 

[MARK858]

Also the

ActiveCell.FormulaR1C1 = _

part shouldn't be there, just...

 Range("N2:N" & LastRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],Codes!C[-13]:C[-12],2,FALSE)),"" "",VLOOKUP(RC[-5],Codes!C[-13]:C[-12],2,FALSE))"

Which would give the below in N2

=IF(ISERROR(VLOOKUP(I2,Codes!A:B,2,FALSE))," ",VLOOKUP(I2,Codes!A:B,2,FALSE))

I doubt though that you want the space in " "

 

[VaYooper]

For that formula you need to use .FormulaR1C1, you also need to double=up on all the quotes inside the formula.

Thanks... guesss I may have deleted the extra one out of there some how.

 

[VaYooper]

Also the

ActiveCell.FormulaR1C1 = _

part shouldn't be there, just...

 Range("N2:N" & LastRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],Codes!C[-13]:C[-12],2,FALSE)),"" "",VLOOKUP(RC[-5],Codes!C[-13]:C[-12],2,FALSE))"

Which would give the below in N2

=IF(ISERROR(VLOOKUP(I2,Codes!A:B,2,FALSE))," ",VLOOKUP(I2,Codes!A:B,2,FALSE))

I doubt though that you want the space in " "

People tell me all the time about the space in there... its a muscle memory issue I have a hard time gettin rid of as that was how I would write it the first couple hundred times when I began writing formulas.

 

[MARK858]

Happy we helped you find a solution.

Btw in your original post you appear to be trying to use some custom formatting in the code (although missing the 1st part of the BB code tags) , if you want to custom format code then you need to use the RICH code tag rather than the VBA code tag for it to display correctly.