increment order numbers - helper cell?

[si3po]

Hi all,

I need to change some code in my UserForm that we use toinsert orders to a WorkBook so that it incrementally updates the order number.Currently I use the code as seen below:

Dim rng As Range
Set rng = Cells(Rows.Count, 1).End(xlUp)
rng.Offset(1) = rng + 1
ws.Cells(iRow, 2).value = Date
ws.Cells(iRow, 3).value = Me.sectref.value
ws.Cells(iRow, 4).value = Me.ptnum.value
ws.Cells(iRow, 5).value = Me.desc.value

…

This has been working well until recently when we added thefunction to cancel/close an order so that when we now close/cancel an order,the line is removed and placed into an archive on another Worksheet. We added thefunction as the orders list was getting very difficult to navigate and wedecided that it was best to only have unsatisfied orders on our ‘Demands’ pageand all cancelled/closed orders archived away elsewhere.

Now when I place a demand, the order number stillincrements, but if the last line was removed after being closed/cancelled, thenew demand placed would take up the same demand number as the one previouslycancelled. For example, my last order number was ‘665’. I am now required to close/cancelit as it has been satisfied, the line above that is order ‘664’, on closing downorder ‘665’, it is removed to the archive leaving ‘664’ as my last line. When Ithen go to place a new order using the UserForm,the new demand placed would be given Order No. 665 – thus causinga duplicate of the order number just closed.

How would I go about creating a helper cell that updates itselfand increments to show the last order number created, thus allowing the UserFormto refence that cell for the next placed order and prevent duplicated demandnumbers should the last line be closed/cancelled for whatever reason.

Any help would be greatly appreciated!

 

[jmacleary]

Hi there. A couple of options spring to mind. One would be to find the biggest number in use on either worksheet and use that+1. Or you could simply designate a single cell on your main worksheet (lets assume its called Orders and you choose cell AA1 as your helper.

Modify your code as follows:

[FONT=Calibri][SIZE=3][COLOR=#000000]Dim rng As Range
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Set rng = Cells(Rows.Count, 1).End(xlUp)
rng.Offset(1) = Worksheets("Orders").Range("AA1").Value + 1
ws.Cells(iRow, 2).value = Date
ws.Cells(iRow, 3).value = Me.sectref.value
ws.Cells(iRow, 4).value = Me.ptnum.value
ws.Cells(iRow, 5).value = Me.desc.value[/COLOR][/SIZE][/FONT]

At the point where the new order is saved, put in a line like:
Worksheets("Orders").Range("AA1").Value = Worksheets("Orders").Range("AA1").Value + 1

Then make sure you put the next order number in to AA1 to 'seed' it.

 

[si3po]

John,

that's great it works perfectly, thank you so much.

 

[jmacleary]

Youre welcome and thanks for the feedback.