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
|