Range.Formula is giving a Run-time error because of variables

Question

Goodmorning everyone,

I'm a beginner with VBA trying out various things. Atm I'm trying to get a formula to be placed inside a row of cells but I'm encountering a problem. I use a number of variables that seem to be causing a problem in my formula. I already changed some things I found on this site (like changing from ActiveCell to Range and adding the rr and r variables.

However this doesn't fix the error message I'm getting. I looked up the error but didn't quite understand what the meaning was.

Could anyone tell me what is causing the problem in my code but also what the error message means so I can try and debug it myself.

With F8 I already made sure that all variables were filled in correctly. the error comes from the Range(r).Formula line.

Sub Zoeker()

Sheets("Invoer").Select
NumRows_Gist = Range("A3", Range("A3").End(xlDown)).Rows.Count
NumRows_Vand = Range("I3", Range("I3").End(xlDown)).Rows.Count

For x = 1 To NumRows_Vand

Dim r As String

rr = Range("O" & 2 + x).Row
r = "O" & Range("O" & 2 + x).Row

Range(r).Formula = "=IF(IF(IFERROR(VLOOKUP(I" & rr & ";$A$3:$B$" & NumRows_Gist & ";2;FALSE);'Niet vorige upload') <> 'Niet vorige upload'; VLOOKUP(I" & rr & ";$A$3:$B$" & NumRows_Gist & ";2;FALSE) + J" & rr & "; 'correct') <> 0; ''; '0')"

Next

End Sub

Hi, Shouldn't those semicolons in the vlookup formulas be commas? Or is that a excel difference for different language version? — Joe, Mar 19 '21 at 12:08
@JoeJ Hi, I'm not sure but in my Excel I use semicolons inbetween the parameters. I made the formula first and checked it and now I'm trying to bring it into the Macro I'm running. So the formula itself works as intended. — Yvalson Dronkers, Mar 19 '21 at 12:13
In VBA, you have to put the formula in *English* format, with English function names and comma as parameter-separator. That way, you code remains independent on the language used in Excel. Alternative is to use `FormulaLocal` - but with that you need to be sure that the language of Excel is always the same — FunThomas, Mar 19 '21 at 12:17
@FunThomas I Changed the semicolons, but this makes no difference. I don't think the Semicolons were causing errors. — Yvalson Dronkers, Mar 19 '21 at 12:25
The semicolons must be changed to commas, and the text should be in double quotes, not single. (you have to double up the double quotes as they are part of a quoted string). — Rory, Mar 19 '21 at 12:35

score 1 · Accepted Answer · answered Mar 19 '21 at 12:37

Excel Formulas in VBA

Try the following:

Range(r).Formula = "=IF(IF(IFERROR(" _
    & "VLOOKUP(I" & rr & ",$A$3:$B$" & NumRows_Gist & ",2,FALSE)," _
    & """Niet vorige upload"")<>""Niet vorige upload""," _
    & "VLOOKUP(I" & rr & ",$A$3:$B$" & NumRows_Gist & ",2,FALSE)+J" _
    & rr & ",""correct"")<>0, """", ""0"")"

I used the following procedure until I got it right:

Option Explicit

Sub testFormula()
    Const rr As Long = 5
    Const NumRows_Gist As Long = 100
    Dim s As String
    s = "=IF(IF(IFERROR(" _
        & "VLOOKUP(I" & rr & ",$A$3:$B$" & NumRows_Gist & ",2,FALSE)," _
        & """Niet vorige upload"")<>""Niet vorige upload""," _
        & "VLOOKUP(I" & rr & ",$A$3:$B$" & NumRows_Gist & ",2,FALSE)+J" _
        & rr & ",""correct"")<>0, """", ""0"")"
    Debug.Print s
End Sub

@Rory and VBasic Thank you. The double quotes and commas were the problem indeed. — Yvalson Dronkers, Mar 19 '21 at 12:44

score 1 · Answer 2 · answered Mar 19 '21 at 12:44

Writing formulas with VBA is tricky. You have at least the following mistakes in your formula:

You need to write the formula with comma as separator, not semicolon
You need to write strings within the formula with double quotes, not single quotes. But as the VBA compiler would see a double quote character as end of string, you need to double it: 'Niet vorige upload' should be ""Niet vorige upload"", and at the end the empty string needs to be specified as """"

You should always write the formula into an intermediate variable and check the content using the debugger.

dim f as string
f = "=IF(IF(IFERROR(VLOOKUP ...  ' <- Put your formula here
Debug.print f

To get an idea how you need to specify a formula in VBA, change to Excel, enter the desired formula into a cell, change back to the VBA editor and enter ? activeCell.formula into the immediate window.

Thank you for the tips. I come from a C++ and PHP Background (few years ago) and I guess I made some errors that way :) — Yvalson Dronkers, Mar 19 '21 at 12:47

Range.Formula is giving a Run-time error because of variables

2 Answers2

Excel Formulas in VBA