number cells within a range
Thanks Nick, worked a treat!
"NickHK" wrote:
OK, try this:
Private Sub CommandButton1_Click()
Dim Cell As Range
Dim i As Long
Const STARTVAL As Long = 20
For Each Cell In Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0))
If Range("A" & Cell.Row).Value = "" Then Exit For
Cell.Value = STARTVAL + i
i = i + 1
Next
End Sub
Or if you have a lot of cells to fill, this avoids a time consuming loop.
<Briefly tested
Private Sub CommandButton1_Click()
Dim LastRowInActiveCol As Long
Dim LastRowInACol As Long
Dim FillRange As Range
Const STARTVAL As Long = 20
LastRowInActiveCol = ActiveCell.End(xlDown).Offset(-1, 0).Row
LastRowInACol = Range("A" & ActiveCell.Row).End(xlDown).Row
If LastRowInActiveCol LastRowInACol Then
Set FillRange = Range(ActiveCell, ActiveCell.Offset(LastRowInACol -
ActiveCell.Row, 0))
Else
Set FillRange = Range(ActiveCell, ActiveCell.Offset(LastRowInActiveCol -
ActiveCell.Row, 0))
End If
FillRange.FormulaR1C1 = "=R[-1]C+1"
FillRange(1, 1).Value = STARTVAL
End Sub
NickHK
"PeteN" wrote in message
...
Thanks Nick for your help.
The routine runs but does not stop if there is an entry in the column,
only
when column A is blank, therefore overwriting entries. What can I do to
stop
this?
"NickHK" wrote:
Here's one way:
Private Sub CommandButton2_Click()
Dim Cell As Range
Dim ProcessRange As Range
Dim i As Long
Const STARTVAL As Long = 20 - 1
Set ProcessRange = Range(ActiveCell, ActiveCell.End(xlDown))
i = 1
Do Until Range("A" & ProcessRange(i).Row).Value = ""
ProcessRange(i).Value = STARTVAL + i
i = i + 1
Loop
End Sub
NickHK
"PeteN" wrote in message
...
I wish to select a cell and then sequencially number it (starting at
20)
and
all cells, in the same column, beneath it until either there is an
entry
in
the cell or column A is empty. Is this possible?
Thanks
|