Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
I can select several sheets with
Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
Sub test()
Dim arr() As Long Dim i As Long, n As Long ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1)) n = 1 For i = 2 To 45 arr(n) = i n = n + 1 Next For i = 60 To 99 arr(n) = i n = n + 1 Next Sheets(arr).Select End Sub I didn't test as written above but something similar with a smaller number of sheets worked. Regards, Peter T "excelent" wrote in message ... I can select several sheets with Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
excelent job Peter thanks :-)
"Peter T" skrev: Sub test() Dim arr() As Long Dim i As Long, n As Long ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1)) n = 1 For i = 2 To 45 arr(n) = i n = n + 1 Next For i = 60 To 99 arr(n) = i n = n + 1 Next Sheets(arr).Select End Sub I didn't test as written above but something similar with a smaller number of sheets worked. Regards, Peter T "excelent" wrote in message ... I can select several sheets with Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
... is it possible to just add say from 2-45 and 60 to 99 ?
Just for the fun challenge of not using loops... Sheets([Transpose(Row(2:45))]).Select Sheets([Transpose(Row(60:99))]).Select (False) -- HTH :) Dana DeLouis Windows XP & Office 2003 "excelent" wrote in message ... excelent job Peter thanks :-) "Peter T" skrev: Sub test() Dim arr() As Long Dim i As Long, n As Long ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1)) n = 1 For i = 2 To 45 arr(n) = i n = n + 1 Next For i = 60 To 99 arr(n) = i n = n + 1 Next Sheets(arr).Select End Sub I didn't test as written above but something similar with a smaller number of sheets worked. Regards, Peter T "excelent" wrote in message ... I can select several sheets with Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
Dana,
That is very clever and not something I would have ever thought of. I did discover that it throws a Type Mismatch error if R1C1 reference style is used. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Dana DeLouis" wrote in message ... is it possible to just add say from 2-45 and 60 to 99 ? Just for the fun challenge of not using loops... Sheets([Transpose(Row(2:45))]).Select Sheets([Transpose(Row(60:99))]).Select (False) -- HTH :) Dana DeLouis Windows XP & Office 2003 "excelent" wrote in message excelent job Peter thanks :-) "Peter T" skrev: Sub test() Dim arr() As Long Dim i As Long, n As Long ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1)) n = 1 For i = 2 To 45 arr(n) = i n = n + 1 Next For i = 60 To 99 arr(n) = i n = n + 1 Next Sheets(arr).Select End Sub I didn't test as written above but something similar with a smaller number of sheets worked. Regards, Peter T "excelent" wrote in message I can select several sheets with Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
I did discover that it throws a Type Mismatch error if R1C1 reference
style is used. Hi Jim. Thanks for the feedback on the R1C1 reference! One option if using R1C1 would be to add an "R" in front of the row numbers. Sheets([Transpose(Row(R2:R45))]).Select Sheets([Transpose(Row(R60:R99))]).Select False -- Thanks again. :) Dana DeLouis Windows XP & Office 2003 "Jim Cone" wrote in message ... Dana, That is very clever and not something I would have ever thought of. I did discover that it throws a Type Mismatch error if R1C1 reference style is used. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Dana DeLouis" wrote in message ... is it possible to just add say from 2-45 and 60 to 99 ? Just for the fun challenge of not using loops... Sheets([Transpose(Row(2:45))]).Select Sheets([Transpose(Row(60:99))]).Select (False) -- HTH :) Dana DeLouis Windows XP & Office 2003 "excelent" wrote in message excelent job Peter thanks :-) "Peter T" skrev: Sub test() Dim arr() As Long Dim i As Long, n As Long ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1)) n = 1 For i = 2 To 45 arr(n) = i n = n + 1 Next For i = 60 To 99 arr(n) = i n = n + 1 Next Sheets(arr).Select End Sub I didn't test as written above but something similar with a smaller number of sheets worked. Regards, Peter T "excelent" wrote in message I can select several sheets with Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
Hi Dana,
Pretty cute :-) Would you know how to make an array that way without hard-coding the values. a = 2: b = 4 Arr = [transpose(row(a:b))] Arr becomes 1 to 65536 populated with respective element numbers and regardless of the values a & b. Regards, Peter T "Dana DeLouis" wrote in message ... ... is it possible to just add say from 2-45 and 60 to 99 ? Just for the fun challenge of not using loops... Sheets([Transpose(Row(2:45))]).Select Sheets([Transpose(Row(60:99))]).Select (False) -- HTH :) Dana DeLouis Windows XP & Office 2003 "excelent" wrote in message ... excelent job Peter thanks :-) "Peter T" skrev: Sub test() Dim arr() As Long Dim i As Long, n As Long ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1)) n = 1 For i = 2 To 45 arr(n) = i n = n + 1 Next For i = 60 To 99 arr(n) = i n = n + 1 Next Sheets(arr).Select End Sub I didn't test as written above but something similar with a smaller number of sheets worked. Regards, Peter T "excelent" wrote in message ... I can select several sheets with Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
Sub ABC()
Dim a As Long, b As Long a = 2: b = 4 arr = Evaluate("transpose(row(" & _ a & ":" & b & "))") MsgBox "Arr: " & arr(LBound(arr)) & "-" & arr(UBound(arr)) End Sub -- Regards, Tom Ogilvy "Peter T" <peter_t@discussions wrote in message ... Hi Dana, Pretty cute :-) Would you know how to make an array that way without hard-coding the values. a = 2: b = 4 Arr = [transpose(row(a:b))] Arr becomes 1 to 65536 populated with respective element numbers and regardless of the values a & b. Regards, Peter T "Dana DeLouis" wrote in message ... ... is it possible to just add say from 2-45 and 60 to 99 ? Just for the fun challenge of not using loops... Sheets([Transpose(Row(2:45))]).Select Sheets([Transpose(Row(60:99))]).Select (False) -- HTH :) Dana DeLouis Windows XP & Office 2003 "excelent" wrote in message ... excelent job Peter thanks :-) "Peter T" skrev: Sub test() Dim arr() As Long Dim i As Long, n As Long ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1)) n = 1 For i = 2 To 45 arr(n) = i n = n + 1 Next For i = 60 To 99 arr(n) = i n = n + 1 Next Sheets(arr).Select End Sub I didn't test as written above but something similar with a smaller number of sheets worked. Regards, Peter T "excelent" wrote in message ... I can select several sheets with Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with - Sheets(Array(1, 2, 3)).Select
Ah, of course. Like passing arguments with Ontime.
Thanks for that. Regards, Peter T "Tom Ogilvy" wrote in message ... Sub ABC() Dim a As Long, b As Long a = 2: b = 4 arr = Evaluate("transpose(row(" & _ a & ":" & b & "))") MsgBox "Arr: " & arr(LBound(arr)) & "-" & arr(UBound(arr)) End Sub -- Regards, Tom Ogilvy "Peter T" <peter_t@discussions wrote in message ... Hi Dana, Pretty cute :-) Would you know how to make an array that way without hard-coding the values. a = 2: b = 4 Arr = [transpose(row(a:b))] Arr becomes 1 to 65536 populated with respective element numbers and regardless of the values a & b. Regards, Peter T "Dana DeLouis" wrote in message ... ... is it possible to just add say from 2-45 and 60 to 99 ? Just for the fun challenge of not using loops... Sheets([Transpose(Row(2:45))]).Select Sheets([Transpose(Row(60:99))]).Select (False) -- HTH :) Dana DeLouis Windows XP & Office 2003 "excelent" wrote in message ... excelent job Peter thanks :-) "Peter T" skrev: Sub test() Dim arr() As Long Dim i As Long, n As Long ReDim arr(1 To (45 - 2 + 1) + (99 - 60 + 1)) n = 1 For i = 2 To 45 arr(n) = i n = n + 1 Next For i = 60 To 99 arr(n) = i n = n + 1 Next Sheets(arr).Select End Sub I didn't test as written above but something similar with a smaller number of sheets worked. Regards, Peter T "excelent" wrote in message ... I can select several sheets with Sheets(Array(1, 2, 3)).Select but if i ned to select many sheets, say abot 100 how do i add the array to do that without writing 1,2,3,4,5,6,7,... manualy ? by the way is it possible to just add say from 2-45 and 60 to 99 ? I cant figure this out, so any help would be greatfull thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sheets(array).select problem | Excel Discussion (Misc queries) | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Select sheets from an array for printing | Excel Discussion (Misc queries) | |||
Using an array to select data | Excel Programming | |||
Select Sheets via Array Macro | Excel Programming |