IF conditional formula in VBA - Run-time error '1004'

Question

I am getting a Run-time error '1004' at the line with IF statement. Checked the previous posts, but could not find a relevant solution. My code is

Sub RefreshFormulae()
Set sh = ActiveSheet
lastR = sh.Range("B" & sh.Rows.Count).End(xlUp).Row
sh.Range("$J$3:$K$" & lastR).ClearContents
sh.Range("$H$3:$H$" & lastR).Formula = "=$G3"
sh.Range("$K$3:$K$" & lastR).Formula = "=IF($I3<>""Not Found"";$I3;"""")"
End Sub

Error is in the line below. Am I missing anything here?

sh.Range("$K$3:$K$" & lastR).Formula = "=IF($I3<>""Not Found"";$I3;"""")"

Has been asked so many times here on SO, but too tired to look for the best fitting duplicate. In VBA, you need to specify your formula in the english format, replace `";"` with `","` — FunThomas, Sep 15 '21 at 11:28

ed2 · Answer 1 · 2021-09-15T11:33:48.293

2

You need to use a comma (,) instead of a semicolon (;) in VBA, as the regional format for separators inside functions only works in the Excel formula bar interface, not macros/VBA.

Try: "=IF($I3<>" & Chr(34) & "Not Found"",$I3,"""")"

This would make the whole block change to:

Sub RefreshFormulae()
Set sh = ActiveSheet
lastR = sh.Range("B" & sh.Rows.Count).End(xlUp).Row
sh.Range("$J$3:$K$" & lastR).ClearContents
sh.Range("$H$3:$H$" & lastR).Formula = "=$G3"
sh.Range("$K$3:$K$" & lastR).Formula = "=IF($I3<>" & Chr(34) & "Not Found"",$I3,"""")"
End Sub

edited Sep 15 '21 at 11:33

answered Sep 15 '21 at 11:25

ed2

1,457
1
9
26

Don't know why this answer was upvoted. Problem of OP is *not* quotes, they are correct. Problem is you need to use comma, not semicolon – FunThomas Sep 15 '21 at 11:33
Updated accordingly, thanks @FunThomas – ed2 Sep 15 '21 at 11:34
yes, replacing ; with , solved the problem. thank you – Ushay Sep 15 '21 at 11:36
Your first sentence is misleading. `;` works with the `Local` versions: `.FormulaLocal`, `.Formula2Local`, etc. – BigBen Sep 15 '21 at 12:45

score 0 · Accepted Answer · answered Sep 15 '21 at 11:29

0

You need to use commas when entering formulas in VBA, even when you use semicolon directly in the sheet.

Sub RefreshFormulae()
Set sh = ActiveSheet
lastR = sh.Range("B" & sh.Rows.Count).End(xlUp).Row
sh.Range("$J$3:$K$" & lastR).ClearContents
sh.Range("$H$3:$H$" & lastR).Formula = "=$G3"
sh.Range("$K$3:$K$" & lastR).Formula = "=IF($I3<>""Not Found"",$I3,"""")"
End Sub

answered Sep 15 '21 at 11:29

tnavidi

1,043
6
12

thank you! As pointed out here ; was creating the problem – Ushay Sep 15 '21 at 11:33
You only need to use commas if you're using `.Formula`. Your first sentence is misleading, since `.FormulaLocal` with `;` is entirely possible. – BigBen Sep 15 '21 at 12:46

IF conditional formula in VBA - Run-time error '1004'

2 Answers2