PDA

View Full Version : Solved: Continuing the numbering



Aussiebear
08-03-2006, 03:31 AM
Is there a way of getting Excel to remember the last highest number entered, so that if a row was deleted, you could enter the next sequential number even if it was in the gap just created by the row just deleted?

Running down a column with A1 +1 comes unstuck if you alter a cell in the range.

Bob Phillips
08-03-2006, 03:47 AM
How about just using

=ROW()

Aussiebear
08-03-2006, 03:52 AM
Looked up =Row() in Help but its still a mystery to me

lucas
08-03-2006, 02:06 PM
Might try a sheet change:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim RowOffset As Long
Dim IndexCol As String
'Set values
RowOffset = 0
'Change the A to the column where you want the numbers to show
IndexCol = "A"

Intersect(ActiveCell.EntireRow, Columns(IndexCol)).Value = ActiveCell.Row + RowOffset
End Sub

Aussiebear
08-04-2006, 03:03 AM
Thanks Steve.

I had come across this one late last night, and while at work I found that if you deleted a row, and added the next highest number, it was duplicated when selecting the next cell down from the las tof the row. Will give yours a trundle and see what runs up the flagpole.



Private Sub WorkSheet_SelectionChange(ByVal Target As Excel.Range)
RowOffset = O
Intersect (ActiveCel.Entire.Row, Colummns("A")).Value = ActiveCell.Row + RowOffset
End Sub

Aussiebear
08-06-2006, 04:06 PM
OKay, I need to attack this from another method.

Is it possible to have a hidden cell remember the highest value recorded within a range ( even if that highest value has since been deleted)?

The purpose here is to enable a continuace of a numbering pattern within a set range of rows.

Aussiebear
08-07-2006, 09:01 AM
I've tried using Max & MaxA for the hidden cell value but as soon as you delete the row with the highest value in it, the value of the hidden cell drops back to the next possible higest value. What I'd like is for the value of the hidden cell to be retained once its equivilant value has been used in the range A4: A28.

Any ideas anyone?

mdmackillop
08-07-2006, 09:37 AM
Hi Ted,
You need to add the highest value to A2 initially. The number format can be removed after the first running. If you're going to need 3 figure numbers, you may want to use the formatting alternative.
Regards
MD

Sub Macro1()
[a2].NumberFormat = ";;;"
[a2] = [a2] + 1
ActiveCell.Formula = "2006-" & [a2]
'ActiveCell.Formula = "2006-" & Format([a2], "000")
End Sub

Zack Barresse
08-07-2006, 09:39 AM
Why is =ROW() a mystery?

You enter into the cell ...

=ROW()

.. just as it's shown there and the row number will appear (it's a cell formula).

Aussiebear
08-07-2006, 09:46 AM
Zack, over time I will be re-using that same row, so today it might be 2006-01 and in a month's time it could be 2006-33.

Malcolm, The numbers in the range will start with 2006-01 and build until we change to a new year where it will start with 2007-01 and build upwards

mdmackillop
08-07-2006, 09:53 AM
Then I would use

ActiveCell.Formula = Year(Now()) & "-" & Format([a2], "000")

remembering to reset A2 to 0 after your New Year celebrations.

Aussiebear
08-07-2006, 10:54 AM
Malcolm, your code works but only for the first time you use the range. This is why the "=Row() function doesn't work as well. The objective here is to be able to use any of the rows within a Range, (say A2:A25), more than once and yet be able to continue the numbering in sequence from the last highest number used.

Perhaps the only way around this issue is to set up a user form, (Called by a button on the main sheet) which has a counter type function included.

mdmackillop
08-07-2006, 11:19 AM
A sample is required

mdmackillop
08-07-2006, 11:31 AM
Is there a way of getting Excel to remember the last highest number entered, so that if a row was deleted, you could enter the next sequential number even if it was in the gap just created by the row just deleted?
You need to be careful with your teminology. There will be no gap if a row is deleted. There will be a gap if a row is cleared.

Aussiebear
08-07-2006, 11:45 AM
Sorry for the wrong terminology. Your code works very well and is exactly what I'm looking. Thank you Malcolm (You shold change your name to "Solver")

Aussiebear
08-09-2006, 01:42 AM
OKay, I'm back to square one again. It's become like a zig saw puzzle with all black pieces....... Can someone please wave their magic wand over this?

Aussiebear
08-09-2006, 01:43 AM
I'm away for at least a day, but the Admin sheet gives a rough idea of what its meant to do.

mdmackillop
08-09-2006, 01:06 PM
Here's a userform solution

Aussiebear
08-10-2006, 03:22 PM
Thank you Malcolm.

mdmackillop
08-12-2006, 06:34 AM
Userform updated