Excel Formula works in conditional formatting but doesn't in code

Question

I have a problem with a formula in VBA. It works in conditional formatting and in excel itself, but I can't use it from VBA level

This is the code:

Range("A1").Formula = "=IF(ISERR(FIND(" & Chr(34) & "-" & Chr(34) & ";" & s_adr_1 & ";2))=FALSE;TRUE;IF(ISERR(FIND(" & Chr(34) & "…" & Chr(34) & ";" & s_adr_1 & ";2))=FALSE;TRUE;IF(ISERR(FIND(" & Chr(34) & ".." & Chr(34) & ";" & s_adr_1 & "))=FALSE;TRUE;IF(ISBLANK(" & s_adr_1 & ");FALSE;IF(AND(IF(ISERR(RIGHT(" & s_adr_1 & ")*1);NOT(ISERR(FIND(" & s_adr_2 & ";" & s_adr_1 & ")));TRUE));FALSE;IF(LEFT(" & s_adr_1 & ")=""#"";FALSE;ISERR(FIND(" & s_adr_2 & ";" & s_adr_1 & "))))))))"

Where s_adr_1 and s_adr_2 are a references to cells so it can look like this as a sample (in B1 there will be my test text, like e.g. "5-15", "15", "5...15"):

Range("A1").Formula = "=IF(ISERR(FIND(" & Chr(34) & "-" & Chr(34) & ";" & "B1" & ";2))=FALSE;TRUE;IF(ISERR(FIND(" & Chr(34) & "…" & Chr(34) & ";" & "B1" & ";2))=FALSE;TRUE;IF(ISERR(FIND(" & Chr(34) & ".." & Chr(34) & ";" & "B1" & "))=FALSE;TRUE;IF(ISBLANK(" & "B1" & ");FALSE;IF(AND(IF(ISERR(RIGHT(" & "B1" & ")*1);NOT(ISERR(FIND(" & "C1" & ";" & "B1" & ")));TRUE));FALSE;IF(LEFT(" & "B1" & ")=""#"";FALSE;ISERR(FIND(" & "C1" & ";" & "B1" & "))))))))"

Expected outcome in A1 would be:

=IF(ISERR(FIND("-";B1;2))=FALSE;TRUE;IF(ISERR(FIND("…";B1;2))=FALSE;TRUE;IF(ISERR(FIND("..";B1))=FALSE;TRUE;IF(ISBLANK(B1);FALSE;IF(AND(IF(ISERR(RIGHT(B1)*1);NOT(ISERR(FIND(C$1;B1)));TRUE));FALSE;IF(LEFT(B1)="#";FALSE;ISERR(FIND(C$1;B1))))))))

It should return TRUE or FALSE.

When I try to run the code I got Error 1004 :(

What is wrong here? Why it works when I paste it in Excel manually?

Why the Reopen vote? What solution to this is not adequately covered multiple times by the three duplicate examples I provided? — , Feb 13 '18 at 10:37
It didn't work when I changed ";" to ",". This formula has to be inserted and work in Excel sheet, so I don't need the outcome but a formula in a cell — Mariusz Wach, Feb 13 '18 at 10:43
@Jeeped - when I have voted it was only one - "Different Languages ..." and this was not the case. — Vityata, Feb 13 '18 at 10:54

Vityata · Accepted Answer · 2018-02-13T10:31:52.983

0

Assuming that you are using English Excel version, simply use double quotes in the VBA version. Or pretty much do the following:

write the formula in Excel and select the cell, where it is
run this code and mimic the formula, that you get:

-

Public Sub PrintMeUsefulFormula()

    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.Formula
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula

End Sub

edited Feb 13 '18 at 10:31

answered Feb 13 '18 at 10:24

Vityata

42,633
8
55
100

VBA'S .Formula will not accept semi-colons as a list separator. Either use commas or switch to .FormulaLocal. – Feb 13 '18 at 10:31
It didn't work :( and also the whole code will be used in Polish, Norwegian and Finnish version of Excel :( – Mariusz Wach Feb 13 '18 at 10:50
@MariuszWach - what have you tried? What have you received? What did not work? – Vityata Feb 13 '18 at 10:52
@Vityata I tried your code, I also tried to manually change "-" to ""-"" etc – Mariusz Wach Feb 13 '18 at 10:55
1

@MariuszWach - before trying the code, you should have done 2 things - First writing the formula in Excel correctly and Second - selecting the cell. Have you done these? What have you received in the immediate window? – Vityata Feb 13 '18 at 10:56
@Vityata It worked now, amazing. I pasted wrong formula. Thanks a lot! I received correct formula for VBA =IF(ISERR(FIND("-";K3;2))=FALSE;TRUE;IF(ISERR(FIND("…";K3;2))=FALSE;TRUE;IF(ISERR(FIND("..";K3))=FALSE;TRUE;IF(ISBLANK(K3);FALSE;IF(AND(IF(ISERR(RIGHT(K3)*1);NOT(ISERR(FIND(K$6;K3)));TRUE));FALSE;IF(LEFT(K3)="#";FALSE;ISERR(FIND(K$6;K3)))))))) – Mariusz Wach Feb 13 '18 at 10:59
@MariuszWach - congrats! – Vityata Feb 13 '18 at 11:00

Excel Formula works in conditional formatting but doesn't in code

1 Answers1