Excel VBA - receiving Compile Error: Expected: #

MKR 0
Viewed 47

I would like to set the column width for one column across most of sheets in my workbook.

The code looks like this:

Sub width()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "NAD SHEET" Or ws.Name = "BoM" Or ws.Name = "BoQ" Or ws.Name = "Sign Off Sheet" 
Or ws.Name = "PIANOI" Then
'do nothing
Else
ws.Range("M").ColumnWidth = 12.67
End If
Next ws
End Sub

I am getting an error:

enter image description here

Compile error: Expected: #

I tried also

With
ws.Range("M").ColumnWidth = 12.67
End With

as well as

ws.Range("M1:M").ColumnWidth = 12.67

still the same.

I don't know what is going on really here.

vba excel
1 Answers

I found 2 things in your code that are incorrect.

First, the if statement need to be in 1 line(like @braX said). If you really want it be 2 lines, put a " _" at the end of first line.

Second, like @Rory said, use Range("M:M")

After testing the code, it's running ok on my computer:

 Sub width()
        Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name = "NAD SHEET" Or ws.Name = "BoM" Or ws.Name = "BoQ" Or ws.Name = "Sign Off Sheet" _
            Or ws.Name = "PIANOI" Then
                'do nothing
            Else
                ws.Range("M:M").ColumnWidth = 12.67
            End If
        Next ws
End Sub
lamys0
Related