VBA Excel: Compile error: Syntax error

Question

Sub LoopCertain() 'Excel VBA to loop through only include sheets (1-3)

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets ' Start of the VBA loop
  Select Case sh.Name
    Case Is = "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D13", "D14", "D15", "D16", "D17", "D18", "D19", "D20", "D21", "D22", "D23", "D24", "D25", "D26", "D27", "D28", "D29", "D30", "D31"
     sh.[D15].Value = "=(4-(D16+D17+D18+D19))+(SUMIF(J27:J38,"Operational",N27:N38))"
     sh.[D21].Value = "=4-(SUM(D16:D20))"
     sh.[D23].Value = "=D15/4"
  End Select
Next sh

End Sub

The error is on the line listed below and is caused by "Operational", I assume because it sees the " and tries to end there, how do I make it so it takes "operational" literally as is and continues past it?

sh.[D15].Value = "=(4-(D16+D17+D18+D19))+(SUMIF(J27:J38,"Operational",N27:N38))"

Are you really trying to set the .Value to those strings, or are you trying to set the .Formula to that? — YowE3K
– YowE3K, Commented Oct 4, 2016 at 8:03

Maximilian Peters · Accepted Answer · 2016-10-04 06:10:10Z

1

You can either double the quotation marks or

sh.[D15].Value = "=(4-(D16+D17+D18+D19))+(SUMIF(J27:J38,""Operational"",N27:N38))"

use CHR(34).

sh.[D15].Value = "=(4-(D16+D17+D18+D19))+(SUMIF(J27:J38," + CHR(34) + "Operational" + CHR(34)",N27:N38))"

answered Oct 4, 2016 at 6:10

Maximilian Peters

31.8k12 gold badges95 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

VBA Excel: Compile error: Syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related