Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheets(array).select problem
I've tried various formats of array contents in sheets(array).select, and
always get a subscript out of range error. I've used (1, 2, 3), ("sheet1", "sheet2", "sheet3"), and others. I DO have multiple sheets, and have verified that the ones in the array actually exist. My current code: Dim mySheet As Worksheet For Each mySheet In ActiveWorkbook.Sheets Worksheets(mySheet.Name).Activate If ActiveSheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet If sheetlist = "" Then sheetlist = mySheet.Index Else sheetlist = sheetlist & ", " & mySheet.Index End If End If Next Sheets(sheetlist).Select End Sub Sheetlist contents when it crashes on select statement: 31, 32, 33, 36 But it crashes if I change the code so that sheetlist contents a "sheet31, "sheet32", "sheet33", "sheet36" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheets(array).select problem
You need to create an array
Dim mySheet As Worksheet Dim sheetList Dim i As Long ReDim sheetList(1 To ActiveWorkbook.Sheets.Count) For Each mySheet In ActiveWorkbook.Sheets If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet i = i + 1 arry(i) = mySheet.Name End If Next Sheets(sheetList).Select -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark" wrote in message ... I've tried various formats of array contents in sheets(array).select, and always get a subscript out of range error. I've used (1, 2, 3), ("sheet1", "sheet2", "sheet3"), and others. I DO have multiple sheets, and have verified that the ones in the array actually exist. My current code: Dim mySheet As Worksheet For Each mySheet In ActiveWorkbook.Sheets Worksheets(mySheet.Name).Activate If ActiveSheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet If sheetlist = "" Then sheetlist = mySheet.Index Else sheetlist = sheetlist & ", " & mySheet.Index End If End If Next Sheets(sheetlist).Select End Sub Sheetlist contents when it crashes on select statement: 31, 32, 33, 36 But it crashes if I change the code so that sheetlist contents a "sheet31, "sheet32", "sheet33", "sheet36" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheets(array).select problem
Didn't work. I get compile error when it hits arry(i) line: sub or function
not defined Perhaps you meant sheetlist(i)? But when I do this I get the same error as before when I hit the select command: susbscript out of range. Looking at contents of sheetlist array, it is as expected: the first four values are the four sheet names that have red tabs. After that, the values are null. ?sheetlist(1) 9.17_2ad ?sheetlist(2) 9.17_2ae ?sheetlist(3) 9.17_2af ?sheetlist(4) 9.20_1b ?sheetlist(5) What I find really strange about all this is that if I copy and paste the code from the Excel help file: Referring to More Than One Sheet SpecificsUse the Array function to identify a group of sheets. The following example selects three sheets in the active workbook. Sub Several() Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Select End Sub I get the same subscript out of range error. Other ideas? "Bob Phillips" wrote: You need to create an array Dim mySheet As Worksheet Dim sheetList Dim i As Long ReDim sheetList(1 To ActiveWorkbook.Sheets.Count) For Each mySheet In ActiveWorkbook.Sheets If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet i = i + 1 arry(i) = mySheet.Name End If Next Sheets(sheetList).Select -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark" wrote in message ... I've tried various formats of array contents in sheets(array).select, and always get a subscript out of range error. I've used (1, 2, 3), ("sheet1", "sheet2", "sheet3"), and others. I DO have multiple sheets, and have verified that the ones in the array actually exist. My current code: Dim mySheet As Worksheet For Each mySheet In ActiveWorkbook.Sheets Worksheets(mySheet.Name).Activate If ActiveSheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet If sheetlist = "" Then sheetlist = mySheet.Index Else sheetlist = sheetlist & ", " & mySheet.Index End If End If Next Sheets(sheetlist).Select End Sub Sheetlist contents when it crashes on select statement: 31, 32, 33, 36 But it crashes if I change the code so that sheetlist contents a "sheet31, "sheet32", "sheet33", "sheet36" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheets(array).select problem
Sorry, only had 2000 when I posted, so I didn't test.
Tested now on 2003 it works Dim mySheet As Worksheet Dim sheetList Dim i As Long For Each mySheet In ActiveWorkbook.Sheets If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet i = i + 1 ReDim sheetList(1 To i) sheetList(i) = mySheet.Name End If Next If Not IsEmpty(sheetList) Then Sheets(sheetList).Select -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Mark" wrote in message ... Didn't work. I get compile error when it hits arry(i) line: sub or function not defined Perhaps you meant sheetlist(i)? But when I do this I get the same error as before when I hit the select command: susbscript out of range. Looking at contents of sheetlist array, it is as expected: the first four values are the four sheet names that have red tabs. After that, the values are null. ?sheetlist(1) 9.17_2ad ?sheetlist(2) 9.17_2ae ?sheetlist(3) 9.17_2af ?sheetlist(4) 9.20_1b ?sheetlist(5) What I find really strange about all this is that if I copy and paste the code from the Excel help file: Referring to More Than One Sheet SpecificsUse the Array function to identify a group of sheets. The following example selects three sheets in the active workbook. Sub Several() Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Select End Sub I get the same subscript out of range error. Other ideas? "Bob Phillips" wrote: You need to create an array Dim mySheet As Worksheet Dim sheetList Dim i As Long ReDim sheetList(1 To ActiveWorkbook.Sheets.Count) For Each mySheet In ActiveWorkbook.Sheets If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet i = i + 1 arry(i) = mySheet.Name End If Next Sheets(sheetList).Select -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark" wrote in message ... I've tried various formats of array contents in sheets(array).select, and always get a subscript out of range error. I've used (1, 2, 3), ("sheet1", "sheet2", "sheet3"), and others. I DO have multiple sheets, and have verified that the ones in the array actually exist. My current code: Dim mySheet As Worksheet For Each mySheet In ActiveWorkbook.Sheets Worksheets(mySheet.Name).Activate If ActiveSheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet If sheetlist = "" Then sheetlist = mySheet.Index Else sheetlist = sheetlist & ", " & mySheet.Index End If End If Next Sheets(sheetlist).Select End Sub Sheetlist contents when it crashes on select statement: 31, 32, 33, 36 But it crashes if I change the code so that sheetlist contents a "sheet31, "sheet32", "sheet33", "sheet36" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheets(array).select problem
How about a modified version of Bob's routine:
Option Explicit Sub testme() Dim mySheet As Worksheet Dim sheetList As Variant Dim i As Long ReDim sheetList(1 To ActiveWorkbook.Sheets.Count) i = 0 'I like to initialize my variables For Each mySheet In ActiveWorkbook.Sheets If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet i = i + 1 sheetList(i) = mySheet.Name End If Next If i 1 Then ReDim Preserve sheetList(1 To i) Sheets(sheetList).Select Else MsgBox "no sheets found" End If End Sub Mark wrote: Didn't work. I get compile error when it hits arry(i) line: sub or function not defined Perhaps you meant sheetlist(i)? But when I do this I get the same error as before when I hit the select command: susbscript out of range. Looking at contents of sheetlist array, it is as expected: the first four values are the four sheet names that have red tabs. After that, the values are null. ?sheetlist(1) 9.17_2ad ?sheetlist(2) 9.17_2ae ?sheetlist(3) 9.17_2af ?sheetlist(4) 9.20_1b ?sheetlist(5) What I find really strange about all this is that if I copy and paste the code from the Excel help file: Referring to More Than One Sheet SpecificsUse the Array function to identify a group of sheets. The following example selects three sheets in the active workbook. Sub Several() Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Select End Sub I get the same subscript out of range error. Other ideas? "Bob Phillips" wrote: You need to create an array Dim mySheet As Worksheet Dim sheetList Dim i As Long ReDim sheetList(1 To ActiveWorkbook.Sheets.Count) For Each mySheet In ActiveWorkbook.Sheets If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet i = i + 1 arry(i) = mySheet.Name End If Next Sheets(sheetList).Select -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark" wrote in message ... I've tried various formats of array contents in sheets(array).select, and always get a subscript out of range error. I've used (1, 2, 3), ("sheet1", "sheet2", "sheet3"), and others. I DO have multiple sheets, and have verified that the ones in the array actually exist. My current code: Dim mySheet As Worksheet For Each mySheet In ActiveWorkbook.Sheets Worksheets(mySheet.Name).Activate If ActiveSheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet If sheetlist = "" Then sheetlist = mySheet.Index Else sheetlist = sheetlist & ", " & mySheet.Index End If End If Next Sheets(sheetlist).Select End Sub Sheetlist contents when it crashes on select statement: 31, 32, 33, 36 But it crashes if I change the code so that sheetlist contents a "sheet31, "sheet32", "sheet33", "sheet36" -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sheets(array).select problem
Marvelous. Thanks to both of you.
"Dave Peterson" wrote: How about a modified version of Bob's routine: Option Explicit Sub testme() Dim mySheet As Worksheet Dim sheetList As Variant Dim i As Long ReDim sheetList(1 To ActiveWorkbook.Sheets.Count) i = 0 'I like to initialize my variables For Each mySheet In ActiveWorkbook.Sheets If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet i = i + 1 sheetList(i) = mySheet.Name End If Next If i 1 Then ReDim Preserve sheetList(1 To i) Sheets(sheetList).Select Else MsgBox "no sheets found" End If End Sub Mark wrote: Didn't work. I get compile error when it hits arry(i) line: sub or function not defined Perhaps you meant sheetlist(i)? But when I do this I get the same error as before when I hit the select command: susbscript out of range. Looking at contents of sheetlist array, it is as expected: the first four values are the four sheet names that have red tabs. After that, the values are null. ?sheetlist(1) 9.17_2ad ?sheetlist(2) 9.17_2ae ?sheetlist(3) 9.17_2af ?sheetlist(4) 9.20_1b ?sheetlist(5) What I find really strange about all this is that if I copy and paste the code from the Excel help file: Referring to More Than One Sheet SpecificsUse the Array function to identify a group of sheets. The following example selects three sheets in the active workbook. Sub Several() Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Select End Sub I get the same subscript out of range error. Other ideas? "Bob Phillips" wrote: You need to create an array Dim mySheet As Worksheet Dim sheetList Dim i As Long ReDim sheetList(1 To ActiveWorkbook.Sheets.Count) For Each mySheet In ActiveWorkbook.Sheets If mySheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet i = i + 1 arry(i) = mySheet.Name End If Next Sheets(sheetList).Select -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark" wrote in message ... I've tried various formats of array contents in sheets(array).select, and always get a subscript out of range error. I've used (1, 2, 3), ("sheet1", "sheet2", "sheet3"), and others. I DO have multiple sheets, and have verified that the ones in the array actually exist. My current code: Dim mySheet As Worksheet For Each mySheet In ActiveWorkbook.Sheets Worksheets(mySheet.Name).Activate If ActiveSheet.Tab.ColorIndex = 3 Then 'this is a red tab worksheet If sheetlist = "" Then sheetlist = mySheet.Index Else sheetlist = sheetlist & ", " & mySheet.Index End If End If Next Sheets(sheetlist).Select End Sub Sheetlist contents when it crashes on select statement: 31, 32, 33, 36 But it crashes if I change the code so that sheetlist contents a "sheet31, "sheet32", "sheet33", "sheet36" -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem using newly created user function in Excel 2003 | Excel Discussion (Misc queries) | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |