Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For....Next- Array of worksheets.
I have the following code:
..... Set H1 = Sheet2 Set H2 = Sheet4 Set H3 = Sheet6 Set H4 = Sheet8 Set Summary = Sheet11 For Each ws In Array(H1, H2, H3, H4) ..... I have assigned the worksheet code names to variables H1..H4 and now wish to cycle thru these worksheets. But XL is saying: Runtime error 424: Object Required On the For....Next line. What have I missed? -- Julian Milano |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For....Next- Array of worksheets.
Hi Julian,
Pls try like this. Code: -------------------- For Each ws In Worksheets(Array(H1.Name, H2.Name, H3.Name, H4.Name)) -------------------- --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For....Next- Array of worksheets.
Sub tester15()
Set H1 = Sheet1 Set H2 = Sheet11 Set H3 = Sheet111 Set H4 = Sheet1111 Set Summary = Sheet11111 For Each ws In Array(H1, H2, H3, H4) Debug.Print ws.Name Next End Sub Worked fine for me. Are you using the code name of the sheets? If not you need to use set H1 = Worksheets("Sheet2") -- Regards, Tom Ogilvy Julian Milano wrote in message ... I have the following code: .... Set H1 = Sheet2 Set H2 = Sheet4 Set H3 = Sheet6 Set H4 = Sheet8 Set Summary = Sheet11 For Each ws In Array(H1, H2, H3, H4) .... I have assigned the worksheet code names to variables H1..H4 and now wish to cycle thru these worksheets. But XL is saying: Runtime error 424: Object Required On the For....Next line. What have I missed? -- Julian Milano |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For....Next- Array of worksheets.
Thanks Tom,
I found the problem was in my declaration: ' Dim ws As workSheets It doesn't like this line. I suppose "ws" should be declared as a variant? -- Julian Milano "Tom Ogilvy" wrote in message ... Sub tester15() Set H1 = Sheet1 Set H2 = Sheet11 Set H3 = Sheet111 Set H4 = Sheet1111 Set Summary = Sheet11111 For Each ws In Array(H1, H2, H3, H4) Debug.Print ws.Name Next End Sub Worked fine for me. Are you using the code name of the sheets? If not you need to use set H1 = Worksheets("Sheet2") -- Regards, Tom Ogilvy Julian Milano wrote in message ... I have the following code: .... Set H1 = Sheet2 Set H2 = Sheet4 Set H3 = Sheet6 Set H4 = Sheet8 Set Summary = Sheet11 For Each ws In Array(H1, H2, H3, H4) .... I have assigned the worksheet code names to variables H1..H4 and now wish to cycle thru these worksheets. But XL is saying: Runtime error 424: Object Required On the For....Next line. What have I missed? -- Julian Milano |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For....Next- Array of worksheets.
Actually, ws should be declared as Worksheet (singular).
-- Regards, Tom Ogilvy "Julian Milano" wrote in message ... Thanks Tom, I found the problem was in my declaration: ' Dim ws As workSheets It doesn't like this line. I suppose "ws" should be declared as a variant? -- Julian Milano "Tom Ogilvy" wrote in message ... Sub tester15() Set H1 = Sheet1 Set H2 = Sheet11 Set H3 = Sheet111 Set H4 = Sheet1111 Set Summary = Sheet11111 For Each ws In Array(H1, H2, H3, H4) Debug.Print ws.Name Next End Sub Worked fine for me. Are you using the code name of the sheets? If not you need to use set H1 = Worksheets("Sheet2") -- Regards, Tom Ogilvy Julian Milano wrote in message ... I have the following code: .... Set H1 = Sheet2 Set H2 = Sheet4 Set H3 = Sheet6 Set H4 = Sheet8 Set Summary = Sheet11 For Each ws In Array(H1, H2, H3, H4) .... I have assigned the worksheet code names to variables H1..H4 and now wish to cycle thru these worksheets. But XL is saying: Runtime error 424: Object Required On the For....Next line. What have I missed? -- Julian Milano |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For....Next- Array of worksheets.
Check that - I was thinking of something else - yes, ws should be declared
as Variant. If you wanted ws to be declared as worksheet, then you would do it this way: Sub Tester2() Dim ws As Worksheet Set h1 = Worksheets(1) Set h2 = Worksheets(2) Set h3 = Worksheets(3) For Each ws In Worksheets(Array(h1.Name, h2.Name, h3.Name)) Debug.Print ws.Name Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Actually, ws should be declared as Worksheet (singular). -- Regards, Tom Ogilvy "Julian Milano" wrote in message ... Thanks Tom, I found the problem was in my declaration: ' Dim ws As workSheets It doesn't like this line. I suppose "ws" should be declared as a variant? -- Julian Milano "Tom Ogilvy" wrote in message ... Sub tester15() Set H1 = Sheet1 Set H2 = Sheet11 Set H3 = Sheet111 Set H4 = Sheet1111 Set Summary = Sheet11111 For Each ws In Array(H1, H2, H3, H4) Debug.Print ws.Name Next End Sub Worked fine for me. Are you using the code name of the sheets? If not you need to use set H1 = Worksheets("Sheet2") -- Regards, Tom Ogilvy Julian Milano wrote in message ... I have the following code: .... Set H1 = Sheet2 Set H2 = Sheet4 Set H3 = Sheet6 Set H4 = Sheet8 Set Summary = Sheet11 For Each ws In Array(H1, H2, H3, H4) .... I have assigned the worksheet code names to variables H1..H4 and now wish to cycle thru these worksheets. But XL is saying: Runtime error 424: Object Required On the For....Next line. What have I missed? -- Julian Milano |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array across multiple worksheets | Excel Discussion (Misc queries) | |||
Array formula referencing other worksheets | Excel Worksheet Functions | |||
Two worksheets, one array | Excel Discussion (Misc queries) | |||
printing an array of worksheets | Excel Discussion (Misc queries) | |||
array worksheets | Excel Programming |