Using 'For Each..Next'

[Jeffrey Harris]

I have a spreadsheet with the following format.

Date Fund1 Fund2 Fund3 ... Downloaded
Symbol F1 F2 F3
10/24/03 $11.52 $22.35 $5.36 ... Yes
10/25/03 $11.42 $22.45 $5.35 ... No
10/26/03 $11.47 $22.50 $5.36 ... No


I would like to be able to create a macro to 'download' the rows of data
that have not been downloaded.

For Each aRow in 'rows not downloaded'
download
mark as downloaded
Next aRow

My problem is that I'm not sure how I create the collection of rows that
represent the rows that aren't downloaded. I.e. how do I codify the 'rows
not downloaded'? The downloaded state is in Column 'P'. I'm using Office
XP.

 

[J.E. McGimpsey]

One way:

You could use a collection:

Dim RowsNotDownloaded As New Collection
Dim myRng As Range
Dim i As Long
For Each myRng In Range("P2" & _
Range("P" & Rows.Count).End(xlUp).Row)
If myRng <> "Yes" Then _
RowsNotDownloaded.Add myRng.EntireRow
Next myRng
For i = 1 To RowsNotDownloaded.Count
With RowsNotDownloaded(i)
'download
.Cells(16).Value = "Yes"
End With
Next i

OTOH, for this example, why bother with a collection?

Dim myRng As Range
For Each myRng In Range("P2" & _
Range("P" & Rows.Count).End(xlUp).Row)
If myRng <> "Yes" Then
'download
.Value = "Yes"
End If
Next myRng