Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function

Question

When I add data to my workbook it is necassary to copy the formula from an earlier cell to the new cell(s).

I used the following formula to calculate my growth rate:

=WENN(ODER(K9="";L9="");"";WENNFEHLER((L9-K9)/K9;""))

Since this is very time consuming I want to improve it with a macro and have therefor written the following code:

Sub Growth()

Tabelle3.Range("O9").Formula = "=WENN(ODER(K9="";L9="");"";WENNFEHLER((L9-K9)/K9;""))"

Tabelle3.Range("O9:O14").FillDown


End Sub

However, when I want to run the code "runtime error '1004': application defined or object defined error" occours for this line:

Tabelle3.Range("O9").Formula = "=WENN(ODER(K9="";L9="");"";WENNFEHLER((L9-K9)/K9;""))"

Does anyone know why this error occurs and how I can solve it?

Answer 1

You have two main errors in the code - not escapting the " characters and using .Formula instead of .FormulaLocal. The " characters should be written twice to show once in a string. See this code:

Public Sub TestMe()
    Debug.Print "test"""""
End Sub

It prints test"". The last " is for the end of the string. Concerning the formula, use .FormulaLocal if you want to use the German formulas and double the doublequotes:

Range("O9").FormulaLocal = "=WENN(ODER(K9="""";L9="""");"""";WENNFEHLER((L9-K9)/K9;""""))"

In general, avoid using .FormulaLocal and use .Formula, to make your VBA code compatible with a workbook from Italy or France from example. Something like this will work with any local settings in Excel:

Range("O9").Formula = "=IF(OR(K9="""",L9=""""),"""",IFERROR(((K9-K9)/K9),""""))"

---

What I usually do is the following:

1. Write the formula, so it works in Excel;
2. Select it manually;
3. Run this:

---

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

4. It prints the formula as it should look like in the immediate window;
5. Copy it;

6. In the code above you may replace Selection.Formula with one of the following three (Consider as an example =IF(B1=C1,""Equal"",""Not Equal"")

   • Selection.FormulaLocal (gets the local formula =WENN() for Germany)
     =WENN(B1=C1;""Equal"";""Not equal"")
   • Selection.FormulaR1C1 (gets formula in R1C1 format)
     =IF(RC[1]=RC[2],""Equal"",""Not equal"")
   • Selection.FormulaR1C1Local (gets R1C1 format with local formulas) =WENN(ZS(1)=ZS(2);"Equal";"Not equal")