Excel VBA Syntax Error [duplicate]

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.
– Alex K., Commented Nov 7, 2017 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
– Maldred, Commented Nov 7, 2017 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
– SJR, Commented Nov 7, 2017 at 15:01

Vityata · Accepted Answer · 2017-11-07 14:41:23Z

1

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.

answered Nov 7, 2017 at 14:41

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deon · Accepted Answer · 2017-11-08 12:49:39Z

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 8, 2017 at 12:49

Deon

213 bronze badges

Collectives™ on Stack Overflow

Excel VBA Syntax Error [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related