Macro to add a number to a list

[Steve]

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 Schapel]

Steve,

The focus of this newsgroup is macros in Access, the database program. Your
question appears to be related to a spreadsheet program, probably Excel,
where the concept of macros is quite different to Access macros. You will
have a better chance of a good answer if you re-post your question to an
Excel related newsgroup.

 

[Steve]

As per Steve S's reply - this is Excel not Access but we can help anyway.

You should amend your code to the following:


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"
Exit sub
End If
Next Rng
End Sub

I'm not sure what exactly you're doing & why you're having to re-run your
code but the 'Exit Sub' will quit the procedure when it's filled in a -3.