My "do-loop" skips the center of the macro 1

[JoetheSousChef]

Intention---This little macro is supposed to run down a column of dates. Where there is a #N/A, it changes the cell to TODAY(). It is supposed to stop when it hits the blank cell at the bottom of the column of dates.

Action---When stepped with F8, the cursor moves directly from "Do Until ActiveCell..." to "End Sub", without moving through the "If" statements in the middle.

Resolution---I'm guessing I haven't formulated the "Do Until..." statement correctly. Suggestions gratefully received.

Sub Macro3()


Do Until ActiveCell.Value >= " "
'runs down until it hits a blank cell
    If ActiveCell.FormulaR1C1 = "#N/A" Then
        ActiveCell.FormulaR1C1 = "=TODAY()"
    End If
    ActiveCell.Offset(1, 0).Range("A1").Select
Loop

End Sub

 

[PHV]

You may try this:
Do While ActiveCell.Value > " "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Another starting point (typed, untested):
For Each c In ActiveSheet.UsedRange.Columns(1).Cells
If Application.WorksheetFunction.ISNA(c) Then
c.FormulaR1C1 = "=TODAY()"
End If
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JoetheSousChef]

Thank you.

Great to have a community to ask questions of. Most Excel users look blank if you start into VBA!

Best to all

 

[oziric]

Hi
Your code was OK it only needs the following change

Sub Macro3()

Do Until IsEmpty(ActiveCell.Value)
'runs down until it hits a blank cell
If ActiveCell.FormulaR1C1 = "#N/A" Then
ActiveCell.FormulaR1C1 = "=TODAY()"
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

End Sub

Enjoy!
Ric