Excel VBA Syntax Error

Question

I've made what i thought is a simple script in VBA that performs the specified formula and applies it all the way down column D. When i run it i get a Runtime Error 13 Type mismatch. Ive narrowed it down the actual formula part by replacing the formula with something simple like 1+1 and then it works fine. Any advise?

Type Mismatch error on:

Sub FillFormula()
Range("D2").Formula = "=SUBSTITUTE(D2;" - ";"")"
Range("D2", "D" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

Works fine with:

Sub FillFormula()
Range("D2").Formula = "=1+1"
Range("D2", "D" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

Quotes in quotes must be double quotes to quote them as quotes: `"=SUBSTITUTE(D2, "" - "", """")"` — Alex K., Nov 07 '17 at 14:36
@SJR Correct me if I'm wrong, but I believe there are some European versions of Excel that use `;` instead of `,` — Maldred, Nov 07 '17 at 14:55
@Maldred - in spreadsheets, but I think VBA always uses commas (though could be wrong), although an alternative is to use `FormulaLocal`. — SJR, Nov 07 '17 at 15:01

score 1 · Answer 1 · answered Nov 07 '17 at 14:41

Write the Excel formula on D2 the way you want it to be and make sure it works in Excel.
Select D2 and run the following:

Public Sub PrintMeUsefulFormula()

    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.Formula
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula

End Sub

Use whatever is shown in the immediate window. It should work. I have been using it quite a lot.

score 0 · Answer 2 · answered Nov 08 '17 at 12:49

0

Thanks to all. Used advice from everyone and in no time had it working. Final Formula:

Sub FillFormula()
Range("F2").Formula = "=SUBSTITUTE(D2,""-"","""")"
Range("F2", "F" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

answered Nov 08 '17 at 12:49

Deon

21
3

Excel VBA Syntax Error

2 Answers2