pedi wrote...
>Ok, here is my problem:
>I have a range (say A1:B3) and I am using something like this:
>
>For Each c In Range(A1:B3)
>I=I+1
>Cells(5,I)=c.value
>Next c
>
>If my range looks like :
>A B
>--------
>11 100
>22 200
>33 300
>
>the output column "E" would be:
>11
>100
>22
>200
>33
>300
>
>but I want the "for each loop" to scan the columns first so the output
>would be :
>11
>22
>33
>100
>200
>300
Why screw around with macros to do this? Formulas would be sufficient.
If the topmost result would be in cell E1, use formulas like
=INDEX($A$1:$B$3,MOD(ROWS(E$1:E1)-1,3)+1,INT((ROWS(E$1:E1)+2)/3))
and fill down into E2:E6.
If your example was overly simplified and you need to use VBA for
something more complicated, if you need a specific iteration order,
make it explicit in your code.
Dim r As Range, c As Range
For Each c In rng.Columns
For Each r In c.Cells
'whatever
Next r
Next c
Alternatively, make considered use of Excel/VBA's lack of
orthogonality. While 'For Each x In y' iterates by column then by row
througn range objects, it iterates by first dimension, then second,
then third, etc. through arrays, so for 2D arrays, by row then by
column.
Given your data in A1:B3 in the active worksheet, compare columns E and
F generated by the following macro.
Sub foo()
Dim v As Variant, x As Variant, n As Long
Set v = Range("A1:B3").Cells
n = 1
For Each x In v
Cells(n, 5).Value = x.Value
n = n + 1
Next x
v = Range("A1:B3").Value
n = 1
For Each x In v
Cells(n, 6).Value = x
n = n + 1
Next x
End Sub
Annoying, ain't it?!
|
Similar Threads
