Looping through cell

[axwack]

This has to have been addressed before but I'm having some problems
and I was hoping a guru could help me.

I have a column whose row size is variable. In some of the cells for
that column, there is a blank or null value. I want to place NA in
that value and iterate through the cell until the end.

I seem to be able to get the columns row size but I can't seem to
iterate through each cell and check for the null value.

Here is an example of the row:
J
--------
Mod%

..123
..345
..234

..234

..234 [end]

 

[Guest]

It's not clear what you want, but I'll take a guess.

Dim r as Range, myRange as Range

set myRange = range("C1") '<~~~first cell in range
lRow = cells(rows.count,myrange.column).end(xlup).row

Set myRange = myRange.resize(lRow - myRange.row + 1,1)

For each r in myRange
If isempty(r) then
r.formulaR1C1 = "=NA()"
'or
'r.value = "NA"
end if
next r

HTH,
Barb Reinhardt

 

[Gord Dibben]

Sub foo()
Dim rng1 As Range
Set rng1 = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
For Each cell In rng1
If cell.Value = "" Then
cell.Value = "NA"
End If
Next cell
End Sub


Gord Dibben MS Excel MVP