How To: Refer to first cell in a range

[largeselection]

Hi,

So with some help from users on this board I have put together some code which will define a bunch of ranges for me. I need to reference the first, second, third, etc cells in the ranges I've defined now. How can I do that?


As an example: The code might run through the data and say Range1 = Cells(2,3), Cells(5,3), Cells(15,3) and Cells(25,3).

So now I need to be able to refer to first cell in Range1.offset(0,3) in another part of my code, then continue on to say second cell in Range1.offset(0,3) etc.

In my example, that would mean that I would want the code to be able to refer to:

Range1 first = Cells(2,3).offset(0,3)
Range1 Second = Cells(5,3).offset(0,3)
Range1 third = Cells(15,3).offset(0,3)
Range1 fourth = Cells(25,3).offset(0,3)

This is basically so I can pull the value of another cell a few columns over from where the data resides where the range was created.

 

[Kenneth Hobson]

Sub t()
  Dim Range1 As Range
  Dim r As Range, i As Long, a() As Variant
  
  Cells(2, 3).Value = 23
  Cells(5, 3).Value = 53
  Cells(15, 3).Value = 153
  Cells(25, 3).Value = 253
  
  Set Range1 = Union(Cells(2, 3), Cells(5, 3), Cells(15, 3), Cells(25, 3))
  ReDim a(1 To Range1.Count) As Variant
    For Each r In Range1
      i = i + 1
      a(i) = r.Address
  Next r
  
  MsgBox Range(a(1)).Value, , "First cell value in Range1"
  MsgBox a(1), , "Cell address for first cell in Range1"
  
  MsgBox Range(a(3)).Value, , "Third cell value in Range1"
  MsgBox a(3), , "Cell address for third cell in Range1"  
End Sub

 

[largeselection]

Thanks! This contains exactly what I was looking for.