Compile error with inserting an if statement

Question

I want to insert an if statement in diffent sheets in excel. I therefore wrote the following:

 Sub Final_Insert_Columns()


     Dim month As Variant
     Dim months As Variant
     months = Array("07 AMSTERDAM", "07 ARNHEM")

    For Each month In months

        Sheets(month).Activate
        Range=("n4").Formula = "IF(OR(T4=0;T4="PAS");"X"; (AJ4/AK4))

    Next
 End sub

There seems to be a problem however with this part:

 Range=("n4").Formula = "IF(OR(T4=0;T4="PAS");"X"; (AJ4/AK4))
 Compile error: expected end of statement.

Any thoughts on how I should get this working?

Unrelated to the quotes problem, shouldn't that `Range=("n4")` be `Range("n4")`? A typo while writing the question perhaps? Or I'm missing something? — ssarabando, Aug 24 '15 at 15:28

John Coleman · Answer 1 · 2015-08-24T15:34:02.773

1

Instead of

Range=("n4").Formula = "IF(OR(T4=0;T4="PAS");"X"; (AJ4/AK4))

Use

Range("n4").Formula = "=IF(OR(T4=0, T4=""PAS""),""X"", (AJ4/AK4))"

You need to use "" to escape " inside a string. Also -- I think you need commas rather than semicolons in the formula - but maybe that is a regional setting and you can leave the semicolons alone (I had to use commas to test it on my machine). You also had a stray equals sign at the front and a missing equals sign in the formula string. If you don't include "=" before the IF then Excel will treat it as a string that you are inserting in the cell.

edited Aug 24 '15 at 15:34

answered Aug 24 '15 at 15:19

John Coleman

51,337
7
54
119

2

Note that commas vs semi-colons is an Excel setting defined by default based on Region. – Grade 'Eh' Bacon Aug 24 '15 at 15:20
@Grade'Eh'Bacon Good to know. OP has a rather Dutch name. I wonder if those settings are more common in Holland. – John Coleman Aug 24 '15 at 15:32

score 1 · Answer 2 · edited May 23 '17 at 12:22

Your code needs to be revised to reflect the fact that (1) you need to include actual quotation marks within your formula, where it currently actually just ends VBA's own use of quotation marks, and (2) you need to end the whole thing with quotation marks so that VBA knows that your formula text is complete. Like so:

Range=("n4").Formula = "IF(OR(T4=0;T4=""PAS"":);""X""; (AJ4/AK4))"

Another method is to replace the insertion of quotation marks with the ASCII character 34, as suggested here https://stackoverflow.com/a/28507279/5090027.

Compile error with inserting an if statement

2 Answers2