PDA

View Full Version : Solved: For Each Loop through Worksheets Collection



YellowLabPro
09-10-2007, 10:42 PM
This code is failing.
Is it because I am doing a For Each loop and then trying to exclude one of the items in the collection?

For Each wsSource In wbTarget.Worksheets
If wsSource <> "AttribTable" Then
Columns("A:J").AutoFit
Else
Exit For
End If
Next wsSource

mdmackillop
09-11-2007, 12:31 AM
For Each wsSource In wbTarget.Worksheets
If wsSource.Name <> "AttribTable" Then 'Get a string to compare to a string
wsSource.Columns("A:J").AutoFit 'You need to refer to the WS or the
'ActiveSheet will be changed.
Else
Exit For
End If
Next wsSource

Bob Phillips
09-11-2007, 01:20 AM
YLP,

As we have said before, you have to properly qualify an object/property that you work upon, otherwise it will look at the currently active object/property, if there is one.

YOu should also use the property of the object variable, Name for wsSource. Even if it were the default, I strongly believe in NOT using property defaults, so you should be explicit in using wsSource.Name, partly for documentary purposes, partly to ensure that anyone else who reads it gets it immediately and doesn't waste time checking defaults (if they know there is such a thing), partly because it may not always be so.

YellowLabPro
09-11-2007, 04:28 AM
Thanks Malcolm.

Thanks Bob, I found an example reading later on about completing the line w/ the property, (.Name), after finishing up for the night. It makes proper sense, I cannot recall exactly which post before- but one reason I did not look at this as the reason was you and I were covering something similar and I had the thought in my head that wsSource was the same as wsSource.Name.... obviously that was incorrect. I only explain this to this depth as to point out that I am trying not to be careless in my syntax or logic, just have not put it altogether yet.

Cheers Gents,