About Us

PeteN

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to calculate the number of non-zero cells in range?	Claudia d'Amato	Excel Discussion (Misc queries)	6	August 4th 09 04:40 PM
Divide a range of cells by a number	Kate	Excel Discussion (Misc queries)	3	May 3rd 08 06:36 PM
Convert all cells in a range to a number	gumby[_3_]	Excel Programming	1	February 6th 07 02:42 AM
Finding Number Within Range Then Copying Data Below Number to Cells	[email protected]	Excel Programming	5	October 16th 06 06:32 PM
VBA : Number of Blank Cells in a Range	Sanka[_2_]	Excel Programming	4	June 11th 04 06:09 PM

Menu

About Us