Enumerate Range Values
Let's say you have a range "A1:H2".
The first row represents a column name, and the second row represents a row value,e.g:
ColA ColB
100 200
To enumerate the range values, we could use the following code:
Dim rng As Range, col As Range
Set rng = Range("A1:H2")
For Each col In rng.Columns
Debug.Print col.Cells(1).Value & " -" & col.Cells(2).Value
Next
One Row Range
If you have a one-row range this is how you can enumerate values in this range:
Dim rng as Range, cell as Range
Set rng = Range("A1:H1")
For Each cell In rng
Debug.Print cell.Value
Next
You could also use the For ... Next loop with a counter:
Dim i as Integer
For i = 1 To rng.Cells.Count
Debug.Print rng(i).Value
Next
This loop structure allows you to transpose range values,i.e. display them in a column instead of a row.
Dim rng2 as Range //this is a start cell of the column range
Dim rng as Range
Set rng = Range("A1:H1")
Set rng2 = Range ("A2")
For i = 1 To rng.Cells.Count
rng2.Cells(i, 1).Value = rng(i).Value
Next
