Inserting formula using VBA in Excel does not work

Question

First of all, I have tried to look at all the other examples of adding a formula using VBA, and I think I have tried to apply all the answers in this code

Sub AddFormulas(SheetName As String)
    Dim i As Integer

    'Switch worksheet
    Set Wksht = ThisWorkbook.Worksheets(SheetName)
    Wksht.Activate
    Application.Calculation = xlCalculationManual
    i = 2
    While Not IsEmpty(Cells(i, 1))
        Wksht.Cells(i, 18).Formula = "=IFERROR(VLOOKUP(A" & i & ";" & Wksht.Cells(1, 18) & "!$A:$A;1;FALSE);" & Chr(34) & "MISSING" & Chr(34) & ")"
        i = i + 1
    Wend
    Application.Calculation = xlCalculationAutomatic


End Sub

But still, it gives me this anoying error, that I can't interpret

If I change my line to

Wksht.Cells(i, 18) = "'IFERROR(VLOOKUP(A" & i & ";" & Wksht.Cells(1, 18) & "!$A:$A;1;FALSE);" & Chr(34) & "MISSING" & Chr(34) & ")"

Then I get no error, and the correct formula is added, although as a text string

What is wrong, since it would not add what to me looks like a valid formula?

//V

You must use English version formulas when inserting them via VBA. So ; should be ,. — YowE3K
– YowE3K, Commented Sep 14, 2017 at 9:25
You should use .Formula or .Formulalocal or .Value or .Text ... after Wksht.Cells(x,x) — Tibo
– Tibo, Commented Sep 14, 2017 at 9:34

YowE3K · Accepted Answer · 2017-09-14 09:28:03Z

4

The Formula property requires formulas to be written in English, i.e. English function names (not an issue here) and commas as separators rather than semi-colons.

So your statement should be:

Wksht.Cells(i, 18).Formula = "=IFERROR(VLOOKUP(A" & i & "," & Wksht.Cells(1, 18) & "!$A:$A,1,FALSE)," & Chr(34) & "MISSING" & Chr(34) & ")"

If you don't mind having "portability" issues, you could also use the FormulaLocal property, i.e.

Wksht.Cells(i, 18).FormulaLocal = "=IFERROR(VLOOKUP(A" & i & ";" & Wksht.Cells(1, 18) & "!$A:$A;1;FALSE);" & Chr(34) & "MISSING" & Chr(34) & ")"

answered Sep 14, 2017 at 9:28

YowE3K

24k7 gold badges28 silver badges45 bronze badges

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

1 Comment

Verakso Over a year ago

Thank you! @YowE3K this is why I also love SO. Now I have learned something new and valuable I wasn't aware of before,

Vityata · Accepted Answer · 2017-09-14 09:28:23Z

0

Write the formula as it should be in Excel, select it and run the following code:

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

It should print it as it should be.

answered Sep 14, 2017 at 9:28

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Comments

Kresimir L. · Accepted Answer · 2017-09-14 09:43:34Z

0

I believe your code is giving error becouse of this Wksht.Cells(1, 18) part of line in this row Wksht.Cells(i, 18).Formula = "=IFERROR(VLOOKUP(A" & i & ";" & Wksht.Cells(1, 18) & "!$A:$A;1;FALSE);" & Chr(34) & "MISSING" & Chr(34) & ")". Make sure that Wksht.Cells(1, 18) really contains name of worksheet your are trying to address. If this cell is blank, you will receive previously mentioned error.

answered Sep 14, 2017 at 9:43

Kresimir L.

2,4412 gold badges12 silver badges22 bronze badges

Collectives™ on Stack Overflow

Inserting formula using VBA in Excel does not work

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related