Nested For Each...I thought this would work

Posted by Tim on January 09, 2002 10:42 AM

I thought the following code would work. It sets the column widths to alternating widths. The code works on the first worksheet of a multiple worksheet workbook, but then it stops. I don't get any errors, it just doesn't move onto the next worksheet.

Sub tst()

Dim x

For Each Sheet In ActiveWorkbook.Sheets
x = 0
For Each Column In Sheets("Sheet1").Columns
x = x + 1
If x Mod 2 = 0 Then
Cells(1, x).ColumnWidth = 12
Else
Cells(1, x).ColumnWidth = 1
End If
Next
Next

End Sub

What's wrong?

Posted by Russell Hauf on January 09, 2002 11:09 AM

Try the following. The problem with yours was that you have the "Sheet1" reference - so it will never move to another sheet.


Sub tst()     Dim x As Integer 
Dim sht As Worksheet 

    For Each sht In ActiveWorkbook.Sheets 
        For x = 1 To sht.Columns.Count 

If x Mod 2 = 0 Then 
                Cells(1, x).ColumnWidth = 12 
Else 
                Cells(1, x).ColumnWidth = 1 
End If 

' I also suggest you use something
' like the following:
If x > 100 Then 
Exit For 
End If 
' ...unless you really need to format
' each and every column of the worksheet.
        Next x 
    Next sht 
End Sub

Hope this helps,

Russell

Posted by Damon Ostrander on January 09, 2002 12:34 PM

Hi Tim,

Actually the code is doing exactly what it says, but probably not what you intended it to do.

First, by not qualifying the Cells property, the column widths will only be changed on the active worksheet. Second, by looping through all the columns in Sheet 1 you are looping through Sheet 1's columns once for every sheet in the workbook. I suspect you meant to loop through all worksheets' columns. If my assumptions of what you intended to do are correct, the following code will work for you.

Sub tst()

For Each Sheet In Worksheets
For Each Column In Sheet.Columns
If Column.Column Mod 2 = 0 Then
Column.ColumnWidth = 12
Else
Column.ColumnWidth = 1
End If
Next
Next

End Sub

Note that I changed Sheets to Worksheets so it will still work if you have Chart sheets or other kinds of sheets in your workbook