Navigate through selected range to store values...

[Guest]

Need some ideas on how to do this correctly as I'm failing to figure it out.
I have selected a range of cells in a column (using VBA) and want to store
the value in each to a string array.

Set r = Range("Tardy5") 'Tarydy5 is name for range F6:F36
r.Select
n = r.Rows.Count
ReDim arrTardy5Cells(n)

'Loop through selected cells.
'Set curCell = r.Cells(1, i) moved out of the loop below to test.
For i = 1 To n
arrTardy5Cells(i) = ActiveCell.Value 'store value to array
ActiveCell(i, 0).Activate 'expect active cell to advance
down one cell
Next i

The above loop does not behave as I expected it to, so need to know what I'm
missing. Thanks!

 

[RB Smissaert]

You can just do:

Dim arrTardy5Cells

arrTardy5Cells = Range("Tardy5")

This will be a variant, 1-D, 1-based array, but that is probably no problem.
You can always go trough this array with a double loop, for example:

for r = 1 to ubound(arrTardy5Cells)
for c = 1 to ubound(arrTardy5Cells,2)
msgbox arrTardy5Cells(r,c)
next
next

In any case there is no need to activate cells.

RBS

 

[Guest]

Thanks RB,

Thats way easier. However, there must be an easier way to do what I was
going to do next, so perhaps I had better say what I am fully trying to do.
<g>

I want to copy each row that has a date value in the Tardy5 range and place
it below row 40, being sure not to overwrite one of the rows below row 40.

Thanks! I appreciate the help.

 

[Guest]

Actually figured this out easily enough using the recorder. Appreciate the
help. This method works great.