CODEWITHSUNDEEP
vbaexcel
Verakso
Verakso

Reputation: 466

Inserting formula using VBA in Excel does not work

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 enter image description here

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

Upvotes: 2

Views: 9533

Answers (3)

Kresimir L.
Kresimir L.

Reputation: 2441

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.

Upvotes: 0

Vityata
Vityata

Reputation: 43595

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.

Upvotes: 0

YowE3K
YowE3K

Reputation: 23994

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) & ")"

Upvotes: 4

Related Questions