Jean,

Rows is a property of a range object, not an object, so you need to use:

For i = 1 To (Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1)

HTH,
Bernie
MS Excel MVP


"Jean" wrote in message
oups.com...
Hi Bob,

I tried that, but the problem is when my last cell of my range is
"coincidentally" a blank value, then it doesnt get marked as "Open"

Using my knowledge of iterating and knowing that maybe i should iterate
one cell extra, i tried this then, but it gives a "Rows" error:

For i = 1 To (Cells(Rows.Count,"A").End(xlUp).Row + 1)

?




Bob Phillips wrote:
For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value = "" Then
Cells(i,"A").Value = "Open"
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean" wrote in message
oups.com...
Hi all,

I have some data that I imported from Access into an Excel spreadsheet.
Included in all the columns, are two which contain date values. Some
cells are empty, i.e. there is no date.

What I want to do is replace all the empty cells with the text
,,Open". BUT the tricky part is that I need to do this through VB
code. The hard part is that I dont know beforehand how many records are
imported from Access. If I did know, I could just use some code that
selects the range up to where the first empty cell is met, i.e. like

.Range(.Range("g4"), .Range("h4").End(xlDown))

BUT in my case I have empty cells inside the range already! Therefore I
cannot use that. I was thinking of maybe getting the recordset's
RecordCount property from Access and using that variable to iterate
through the cells in Excel, but thought there may be a less complicated
way of doing this.

Can somebody please suggest a way out of my troubles?

Thanks!

J