error 1004 - please help - (?) regional settings

[neveu]

hello,

i have a code line that works perfectly on my computer but when i send it to some else,
i get the error 1004 when running the macro - i have encountered a similar error and the root cause was the regional setting ( the separator in the formula was semicolon and not coma as per my computer.

any advice or support is greatly appreciate

Range("AA2:AA" & LR1).Select
    Selection.FormulaR1C1 = _
        "=IF(TODAY()-RC[-3])<7,""Blocking Notification sent ""& TODAY()-RC[-3] & "" days ago"",IF(AND(RC[-6]>0,RC[-5]<>0),IF(LEFT(RC[-4],5)=""[fax:"",""FAX EOS"",IF(LEFT(RC[-4],4)=""INVA"",""NO CONTACT"",""MAIL EOS"")),IF(AND(RC[-6]=0,LEFT(RC[-4],5)=""[fax:""),""FAX SOA"",IF(AND(RC[-6]=0,LEFT(RC[-4],4)<>""INVA""),""MAIL SOA"",""NO CONTACT"")))"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Richard Schollar]

Hi

Is this a typo?

"=IF(TODAY()-RC[-3])<7

as that doesn't look right. Using FormulaR1C1 should mean that you can enter the formula using commas rather than semi-colons irrespective of the regional settings

 

[Norie]

You've also got a missing closing parentheses right at the end.

This doesn't error, but you'll need to check the formula.

Range("AA2:AA" & LR1).FormulaR1C1 = _
        "=IF(TODAY()-RC[-3]<7,""Blocking Notification sent ""& TODAY()-RC[-3] & "" days ago"",IF(AND(RC[-6]>0,RC[-5]<>0),IF(LEFT(RC[-4],5)=""[fax:"",""FAX EOS"",IF(LEFT(RC[-4],4)=""INVA"",""NO CONTACT"",""MAIL EOS"")),IF(AND(RC[-6]=0,LEFT(RC[-4],5)=""[fax:""),""FAX SOA"",IF(AND(RC[-6]=0,LEFT(RC[-4],4)<>""INVA""),""MAIL SOA"",""NO CONTACT""))))"

You could make the formula slightly shorter, in the code anyway, by using A1 notation.

 

[neveu]

thanks Norie