Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add number to a list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add number to a list
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add number to a list
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add number to a list
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number list | Excel Worksheet Functions | |||
How do i set up a list that sorts as Number/letter/number in orde | Excel Discussion (Misc queries) | |||
again: number a list | New Users to Excel | |||
number a list | New Users to Excel | |||
How to exclude a list of number from another list? | Excel Programming |