Hi,
I have a weird problem with the following code. When the declaration of s is included, the program stops at the "For Each s" statement caliming to need an object. When I comment it out, the code runs error free and TypeName reports "s" to be of type "Worksheet".
Why does the code fail when the declaration of s is included?
Please Login or Register to view this content.
Last edited by bk1; 03-25-2012 at 02:28 PM. Reason: forgot to ask for notification
I'm not sure if this is the reason but you definitely should rename your Sheets variable as this is the name of an existing Excel VBA collection so you may well get conflicts. Call it something else eg mySheets
Unfortunately, that did not help. However, I do agree with your general viewpoint :-)
Dim s as Variant
Ben Van Johnson
Much obliged :-) Thanks!!! That did the trick.
But why does it have to be a variant when each element of the array is a Worksheet object?Is this documented somewhere?
It's documented in Using Arrays in Help. You are iterating through a array, therefore, the control variable (element) must be of type Variant.
This works, yours did not:
so does:Please Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks