That is why whenever I expect having to deal with a range with more
than 1 area land up writing a function
Sub testIt2()
[A1] = 1
[A2] = 2
[D1] = 4
With Range("A1,D1")
MsgBox .Cells.Count & ", " & .Cells(2) & "," & CellInRng(.Cells, 2)
End With
End Sub
Function CellInRng(aRng As Range, ByVal Idx As Long)
Dim anArea As Range, aCell As Range
For Each anArea In aRng.Areas
If Idx <= anArea.Cells.Count Then
Set CellInRng = anArea.Cells(Idx)
Exit Function
Else
Idx = Idx - anArea.Cells.Count
End If
Next anArea
End Function
I benefited tremendously from a study of the exposition on the Range
object/collection in the XL97 developer edition printed manual. It's a
shame that manuals for subsequent versions have become more and more
like marketing material pushing whatever it is that MS wants to push in
a given version. Though, the introduction to COM add-ins in 2000
(2002?) was useful.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article ,
says...
Sub Demo()
[A1] = 1
[A2] = 2
[D1] = 4
Debug.Print Range("A1,D1").Item(2) ' Returns 2
End Sub