Issue with OR function when writing a formula with VBA?

Question

For reasons I don't understand my vba macro gets stuck on this line:

ActiveCell.Formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""),"",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""),"")"",""),A7))"

For reference, the original manually inputted formula (with fewer speechmarks and which works) is:

=IF(OR(A7="Account",AND(LEFT(A7,2)="ID",A8="Account"),A7=""),"",IF(RIGHT(A7,1)=")",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",100)),100)),"(",""),")",""),A7))

Any suggestions what the issue is please?

I'm guessing it's an issue with the OR function as subsequent lines are enacted no problem:

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),TRIM(MID(A7,4,FIND("" "",MID(A7,4,20)))),F6)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=B7-C7"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=TEXT(IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),C7,H6),""dd/mm/yyyy"")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=TEXT(IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),SUBSTITUTE(MID(MID(A7,FIND(""Posted by"",A7),99),FIND("" on "",MID(A7,FIND(""Posted by"",A7),99))+4,12),"")"",""""),I6),""dd/mm/yyyy"")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),IF(MID(A7,FIND(""Posted by "",A7)-1,1)=""("","""",MID(A7,FIND(""("",A7,FIND(""Posted by "",A7)-20)+1,FIND("": Posted by "",A7)-FIND(""("",A7,FIND(""Posted by "",A7)-20)-1)),J6)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),(MID(A7,FIND(""Posted by"",A7)+10,FIND("" on "",A7,FIND(""Posted by"",A7)+10)-FIND(""Posted by"",A7)-10)),K6)"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),MID(A7,FIND("" "",A7,4)+1,FIND(IF(MID(A7,FIND(""Posted by "",A7)-1,1)=""("",""(Posted by "",""(Manual Journal: Posted by ""),A7)-FIND("" "",A8,4)-2),M6)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),RIGHT(A7,LEN(A7)-FIND("")"",A7,FIND(""Posted by "",A7))-1),N6)"
ActiveCell.Offset(0, -9).Range("A1").Select

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Apr 06 '21 at 11:13

score 1 · Answer 1 · answered Apr 06 '21 at 11:12

You need to double all quotes. Some were missing:

Means if your original formula has something like … A7="" … you need to double them in the string like "… A7="""" …".

Change

ActiveCell.Formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""),"",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""),"")"",""),A7))"

into

ActiveCell.Formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""""),"""",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""""),"")"",""""),A7))"

and it will result in this formula:

=IF(OR(A7="Account",AND(LEFT(A7,2)="ID",A8="Account"),A7=""),"",IF(RIGHT(A7,1)=")",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",100)),100)),"(",""),")",""),A7))

score 1 · Answer 2 · answered Apr 06 '21 at 11:17

Basically, your mistake is that if you want to tell the formula to use an empty string, you need to put """" into the VBA code (eg A7=""""). Doubling a quote tells the VBA compiler to put a quote character into the string, so for 2 quote characters you need 2*2 quotes (assuming that your formula is correct, you have 4 instances of that).

An advice: Write to formula into a variable and assign the variable to the formula-property of the cell. That way, you can easily check it with the debugger:

Dim formula as String
formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""),"",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""),"")"",""),A7))"
Debug.Print formula

This will reveal the problem - output in the immediate window is:

=IF(OR(A7="Account",AND(LEFT(A7,2)="ID",A8="Account"),A7="),",IF(RIGHT(A7,1)=")",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",100)),100)),"(","),")","),A7))`

What you need is

formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""""),"""",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""""),"")"",""""),A7))"

Issue with OR function when writing a formula with VBA?

2 Answers2