Hi,

I am trying to write a macro that when I run it what it does is it looks at
Cell A1 and see's if it is blank. If it is blank it then inputs "-3" and
stops. Then next time I run it, it will look at A1 see it is not blank and
move to A2 input "-3" and stop. And it will continue to add a "-3" each time
I run the macro.

The code I currently have written is below. The problem is however, that it
does not stop. It keeps inputing "-3" down the entire Col A. Please help!

Thanks,

Steve

Sub LastRow()
Dim Rng As Range
For Each Rng In Range("A:a")
Range("A1").Select
If Not Rng.Value = "-3" Then
Rng.Select
ActiveCell.FormulaR1C1 = "-3"
End If
Next Rng
End Sub

Steve,

Don't loop - just find the last cell and check its value:

Sub LastRow()
Dim Rng As Range
Set Rng = Cells(Rows.Count, 1).End(xlUp)
If Rng.Value = "-3" Then Rng.Offset(1, 0).Value = "-3"
If Rng.Value = "" Then Rng.Value = "-3"
End Sub


HTH,
Bernie
MS Excel MVP


"Steve" <(E-Mail Removed)> wrote in message
news:317572C8-D29C-41E9-B595-(E-Mail Removed)...
> Hi,
>
> I am trying to write a macro that when I run it what it does is it looks at
> Cell A1 and see's if it is blank. If it is blank it then inputs "-3" and
> stops. Then next time I run it, it will look at A1 see it is not blank and
> move to A2 input "-3" and stop. And it will continue to add a "-3" each time
> I run the macro.
>
> The code I currently have written is below. The problem is however, that it
> does not stop. It keeps inputing "-3" down the entire Col A. Please help!
>
> Thanks,
>
> Steve
>
> Sub LastRow()
> Dim Rng As Range
> For Each Rng In Range("A:a")
> Range("A1").Select
> If Not Rng.Value = "-3" Then
> Rng.Select
> ActiveCell.FormulaR1C1 = "-3"
> End If
> Next Rng
> End Sub
>

Steve,

Try this

Sub LastRow()
If Range("A1").Value = "" Then
Range("A1").Value = -3
Else
Cells(Cells(Cells.Rows.Count, "A") _
.End(xlUp).Row + 1, 1).Value = -3
End If

End Sub

Mike

"Steve" wrote:

> Hi,
>
> I am trying to write a macro that when I run it what it does is it looks at
> Cell A1 and see's if it is blank. If it is blank it then inputs "-3" and
> stops. Then next time I run it, it will look at A1 see it is not blank and
> move to A2 input "-3" and stop. And it will continue to add a "-3" each time
> I run the macro.
>
> The code I currently have written is below. The problem is however, that it
> does not stop. It keeps inputing "-3" down the entire Col A. Please help!
>
> Thanks,
>
> Steve
>
> Sub LastRow()
> Dim Rng As Range
> For Each Rng In Range("A:a")
> Range("A1").Select
> If Not Rng.Value = "-3" Then
> Rng.Select
> ActiveCell.FormulaR1C1 = "-3"
> End If
> Next Rng
> End Sub
>

see if this does what you want:


Sub LastRow()
Dim Rng As Range

For Each Rng In Range("A:A")

If Not Rng.Value = -3 Then

Rng.Value = -3

Exit For

End If

Next Rng

End Sub
--
jb


"Steve" wrote:

> Hi,
>
> I am trying to write a macro that when I run it what it does is it looks at
> Cell A1 and see's if it is blank. If it is blank it then inputs "-3" and
> stops. Then next time I run it, it will look at A1 see it is not blank and
> move to A2 input "-3" and stop. And it will continue to add a "-3" each time
> I run the macro.
>
> The code I currently have written is below. The problem is however, that it
> does not stop. It keeps inputing "-3" down the entire Col A. Please help!
>
> Thanks,
>
> Steve
>
> Sub LastRow()
> Dim Rng As Range
> For Each Rng In Range("A:a")
> Range("A1").Select
> If Not Rng.Value = "-3" Then
> Rng.Select
> ActiveCell.FormulaR1C1 = "-3"
> End If
> Next Rng
> End Sub
>