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

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

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

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

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

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