IF conditional formula in VBA - Run-time error '1004' [duplicate]-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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