(Solved) – Delimiter issue when inserting formula into Excel from VBA

  • by
(solved)-–-delimiter-issue-when-inserting-formula-into-excel-from-vba

I want to put an if-formula into a cell.
However, I get an error 1004 when I try to run the code (relevant part below).

n = 1.2
.Cells(2, 8).Formula = "=if(" & .Cells(2, 3).Address(False, False) & "=1," & n & ",na())"

If I put the formula into a variable, I can see that vba “saves” the 1.2 as “1,2” which is interpreted as if there are too many arguments in the formula.

So instead of

=if(C2=1,1.2,na())

I get

=if(C2=1,1,2,na())

out of VBA.

If I manually enter

.Cells(2, 8).Formula = "=if(" & .Cells(2, 3).Address(False, False) & "=0,1.2" & ",na())"

The formula comes out right in Excel, but as I need to loop over a data, that is not a sustainable solution.

As I am based in Europe, I suspect regional settings being a part of the problem, but as those are company-wide, I can’t fiddle with that without causing a different set of problems.

I tried

n = 1.2
.Cells(2, 8).Formula = "=if(" & .Cells(2, 3).Address(False, False) & "=1," & CDbl(n) & ",na())"

and a few other small things, none of which worked.

If anyone has a solution to this issue, or see something I missed I’d be happy to hear of it.

Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *