CODEWITHSUNDEEP
excelvbams-office
wedwo
wedwo

Reputation: 398

Excel VBA Error 1004 - Application-defined or object-defined error with double quotes in my formula

I'm trying to add a formula using VBA to a cell in Excel, the (simplified) code is as 

    Dim destRow as integer
    destRow = 107
    Cells(destRow, 19) = "=IF(L" & destRow & "="""";"""";""UNB"")"

`I've also tried the following (in place of the last line):

    Cells(destRow, 19) = "=IF(L" & destRow & "=" & chr(34) & chr(34) & ";" & chr(34) & chr(34) & ";" & chr(34) & "UNB" & chr(34) & ")"

In the immediate window, both produce the correct output:

    =IF(L107="";"";"UNB")

...however, I get error 1004 "Application-defined or object-defined error" on the last line when I run my code.

Any help would be appreciated!

Upvotes: 1

Views: 9868

Answers (2)

BrainO2
BrainO2

Reputation: 1374

You should replace the semicolons ";" in the IF formula for commas ",". They will appear as semicolons depending on your excel configuration when the formula is interpreted.

Upvotes: 1

RobinL
RobinL

Reputation: 11557

I think you probably just need to refer to the relevant worksheet.

Your code would work if it was in the code for a sheet rather than in a module.

So 

Sheet1.Cells(destRow, 19) = "=IF(L" & destRow & "=" & chr(34) & chr(34) & ";" & chr(34) & chr(34) & ";" & chr(34) & "UNB" & chr(34) & ")"

Obviously substitute for the desired sheet if it's not Sheet1

Upvotes: 0

Related Questions