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
– ssarabando, Commented Aug 24, 2015 at 15:28

Community · Accepted Answer · 2017-05-23 12:22:05Z

1

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.

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Aug 24, 2015 at 15:20

Grade 'Eh' Bacon

3,8534 gold badges26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Coleman · Accepted Answer · 2015-08-24 15:34:02Z

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, 2015 at 15:34

answered Aug 24, 2015 at 15:19

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

2 Comments

Grade 'Eh' Bacon Over a year ago

Note that commas vs semi-colons is an Excel setting defined by default based on Region.

John Coleman Over a year ago

@Grade'Eh'Bacon Good to know. OP has a rather Dutch name. I wonder if those settings are more common in Holland.

Collectives™ on Stack Overflow

Compile error with inserting an if statement

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related