Getting a list of the cells in a range (VBA)

[Diolu]

In VBA, if I have a range, how do I get the list of individual cells in the range? The only thing I have in mind is to use a "For Each" loop but that sometimes seems odd. For example, I want to make a macro that swap two cells: one select two cells and run the macro. Here is what I have written:

Sub swap()
    Dim r As Range
    Dim c(1) As Range
    Dim n As Integer
    Dim temp As Variant
    Set r = Selection.Cells
    If r.Cells.Count <> 2 Then
        MsgBox ("Please select exactly two cells")
        Exit Sub
    End If
    n = 0
    For Each s In r
       Set c(n) = s
       n = n + 1
    Next s
    temp = c(0).Formula
    c(0) = c(1).Formula
    c(1) = temp
End Sub

I have written a "For Each" to access the two individual cells that the selection contains. But is there a simpler way? If r is the selection, I have tried r.item(1) and r.item(2), but that only works if the range is contiguous. This confuses me, what does "For Each" does in the end? I have read it loop over a "collection", but what is exactly a "collection" for this purpose? Is there an API to get the element of a "collection" other than using a "For Each" loop?

 

[Yongle]

Wecome to the board @Diolu

In VBA, if I have a range, how do I get the list of individual cells in the range?

2 examples:

Sub ListCellsInRange()
    Dim cel As Range, rng As Range, myList As String
    
    Set rng = Range("A1,D10,E99,G34")
    For Each cel In rng
        myList = myList & vbCr & cel.Address(0, 0)
    Next
    MsgBox myList
    
    myList = ""
    Set rng = Intersect(Range("A:B"), Range("2:4"))
    For Each cel In rng
        myList = myList & vbCr & cel.Address(0, 0)
    Next
    MsgBox myList
End Sub