CODEWITHSUNDEEP
excelvbaexcel-formula
Benoit Bouckaert
Benoit Bouckaert

Reputation: 65

Excel VBA Error 1004 in cell formula replacement

Note : I'm French, so normally I use french functions (e.g. SI for IF or SOMME for SUM) and the default decimals separator is the coma and not the point (e.g. 1,03 for 1.03)

I have to replace many formulas in an Excel workbook, and all of them have the same template, but I could not use the fast fill-in tool, so I'm trying to make a macro for this.

First, here is how the cell currently looks :

='C:\...\[file1.xlsx]'sheeta!$XXa$nna - 'C:\...\[file2.xlsx]'sheetb!$XXb$nnb

So basicely, I want to keep these two addresses (I will name them ad1 and ad2) to make the followig formulas :

=IF(AND(ISNUMBER(VALUE(ad1;"."));ISNUMBER(VALUE(ad2;".")));SUM(VALUE(ad1,".");PRODUCT(-1;VALUE(ad2;".")));"NA")

Which substracts two numbers stored with differents formats, and displays NA if at least one of them is not a number.

Here is the macro I wrote :

Sub tmp()
    Dim c As Range
    Dim adr1 As String
    Dim adr2 As String
    Dim frm As String

    For Each c In Application.Selection.Cells
        adr1 = Split(Split(c.Formula, "=")(1), "-'")(0)
        adr2 = "'" & Split(Split(c.Formula, "=")(1), "-'")(1)

        frm = "=IF(AND(ISNUMBER(VALUE(" & adr1 & ";"".""));ISNUMBER(VALUE(" & adr2 & ";""."")));SUM(VALUE(" & adr1 & ";""."");PRODUCT(-1;VALUE(" & adr2 & ";""."")));""NA"")"

        c.Formula = frm
    Next
End Sub

The error occures on the last action c.Formula = frm.

I've already checked frm's value, and it is good.

I think there is a synthax error on my formula, but I couldn't find it. Can someone help me ?

Thanks in advance !

Upvotes: 1

Views: 267

Answers (1)

Sfagnum
Sfagnum

Reputation: 153

VBA accept only US format formula. US format use , instead ;

        frm = "=IF(AND(ISNUMBER(VALUE(" & adr1 & ",""."")),ISNUMBER(VALUE(" & adr2 & ","".""))),SUM(VALUE(" & adr1 & ","".""),PRODUCT(-1,VALUE(" & adr2 & ","".""))),""NA"")"

Upvotes: 1

Related Questions