Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reference to 3D range
I am trying to do a For..Each..Next on a 3D range but can't quite nail the
range variable. The range is A2:F15 in 9 worksheets. Dim rng As Range, c As Range Set rng = Range(Sheet1:Sheet9!A2:F15) 'here's my problem For Each c in rng If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next Mike F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reference to 3D range
VBA doesn't support 3D ranges. A range object has a parent. A 3D range
can not comply with this. -- Regards, Tom Ogilvy "Mike Fogleman" wrote in message ... I am trying to do a For..Each..Next on a 3D range but can't quite nail the range variable. The range is A2:F15 in 9 worksheets. Dim rng As Range, c As Range Set rng = Range(Sheet1:Sheet9!A2:F15) 'here's my problem For Each c in rng If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next Mike F |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reference to 3D range
Doesn't work that way
Dim rng As Range, c As Range Dim sh As Worksheet For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3")) For Each c In sh.Range("A2:F15") If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next c Next sh -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Mike Fogleman" wrote in message ... I am trying to do a For..Each..Next on a 3D range but can't quite nail the range variable. The range is A2:F15 in 9 worksheets. Dim rng As Range, c As Range Set rng = Range(Sheet1:Sheet9!A2:F15) 'here's my problem For Each c in rng If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next Mike F |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reference to 3D range
Dammit! Guess I'm looping worksheets now. Thanks Tom. I figured if there was
some way you would know it. Mike F "Tom Ogilvy" wrote in message ... VBA doesn't support 3D ranges. A range object has a parent. A 3D range can not comply with this. -- Regards, Tom Ogilvy "Mike Fogleman" wrote in message ... I am trying to do a For..Each..Next on a 3D range but can't quite nail the range variable. The range is A2:F15 in 9 worksheets. Dim rng As Range, c As Range Set rng = Range(Sheet1:Sheet9!A2:F15) 'here's my problem For Each c in rng If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next Mike F |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reference to 3D range
3-D is tough. For example:
Sub dural() Set r1 = Sheets("Sheet1").Range("A1") Set r2 = Sheets("Sheet2").Range("A1") Set rx = Union(r1, r2) End Sub fails instantly because the range can't span more than one sheet. However: Sub drmal() Dim rLoop As Range Dim rcol As Collection Set r1 = Sheets("Sheet1").Range("A1") Set r2 = Sheets("Sheet2").Range("A1") Set rcol = New Collection rcol.Add r1 rcol.Add r2 For Each rLoop In rcol For Each r In rLoop MsgBox (r.Address & r.Parent.Name) Next Next End Sub will work just fine because Collections of ranges can span moe than one sheet. -- Gary''s Student "Mike Fogleman" wrote: I am trying to do a For..Each..Next on a 3D range but can't quite nail the range variable. The range is A2:F15 in 9 worksheets. Dim rng As Range, c As Range Set rng = Range(Sheet1:Sheet9!A2:F15) 'here's my problem For Each c in rng If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next Mike F |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reference to 3D range
or more in the spirit of a 3D range
Dim rng As Range, c As Range Dim sh as Worksheet 'Set rng = Range(Sheet1:Sheet9!A2:F15) 'here's my problem bProcess = False for each sh in worksheets if sh.Name = "Sheet1" then bProcess = True if bProcess then set rng = sh.Range("A2:F15") For Each c in rng If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next End if if sh.Name = "Sheet9" then bProcess = False Next -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Doesn't work that way Dim rng As Range, c As Range Dim sh As Worksheet For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3")) For Each c In sh.Range("A2:F15") If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next c Next sh -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Mike Fogleman" wrote in message ... I am trying to do a For..Each..Next on a 3D range but can't quite nail the range variable. The range is A2:F15 in 9 worksheets. Dim rng As Range, c As Range Set rng = Range(Sheet1:Sheet9!A2:F15) 'here's my problem For Each c in rng If c = "-999" Then c = "" ElseIf c 50 Then c = c / 1000000 End If Next Mike F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Can you get the range reference for each page in a worksheet print range? | Excel Programming | |||
adding reference-to-range control to excel range | Excel Programming | |||
Range Reference | Excel Programming | |||
Subscript out of Range on Range reference | Excel Programming |