VBA to Make Advancing ID numbers

[Chelsea0270516]

Basically I review & reconcile a large invoice & invoice log for work. I'd like to make something that will automatically put the next highest number (base on what was previously entered during the current session) into the Invoice column. A couple things:

• It can't use the existing numbers in the Invoice column as a reference - some of them are very large/out of the norm & I can't change old entries.
• I'd be okay with manually updating the starting Invoice number each time I use the code OR making some file I have open that it references if that would be easier.
• Needs to be in my personal workbook as the log can't be saved with macros.
• Column C is hidden in the active sheet I work in.

If anyone can point me in the right direction I am happy to research & test code, I just don't know what code I should be using so searching has been hard.

My thought was to define a variable as an integer, manually set it equal to something, set it to drop into the invoice column of the active sheet, then redefine the variable as the previous one +1. BUT I am having a horrible time figuring out how to make that last part happen.

Obviously the code below isn't working. I used Integer as my variable, but if there is something better...that is cool too!

Looking at it I can see that every time I run it the starting INV will be 3322610300, I just don't know the right logic to make that happen.

Sub NextInv()
'inserts Inv Sum
'I want to make something that will remember what I previously used - redefine the variable?


Dim Inv As Integer

Inv = 3322610300

ActiveCell.Offset(1, 0).Range("A1:H1").Select
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = Inv

Inv = Inv + 1

End Sub




[TABLE="class: grid, width: 852"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]PO[/TD]
[TD]Store#[/TD]
[TD]Order #[/TD]
[TD]Order Date[/TD]
[TD]Dollar Amount[/TD]
[TD]Packing Slip[/TD]
[TD]Code as[/TD]
[TD]Invoice #[/TD]
[TD]Summary Inv#[/TD]
[/TR]
[TR]
[TD]J0523-2308[/TD]
[TD]2308[/TD]
[TD="align: right"]7118454020[/TD]
[TD="align: right"]5/23/2012[/TD]
[TD="align: right"]$28.11[/TD]
[TD]Yes[/TD]
[TD]7-72080-86500[/TD]
[TD="align: right"]3232889805[/TD]
[TD="align: right"]8030076355[/TD]
[/TR]
[TR]
[TD]J0523-2308[/TD]
[TD]2308[/TD]
[TD="align: right"]7118454020[/TD]
[TD="align: right"]5/23/2012[/TD]
[TD="align: right"]$87.18[/TD]
[TD]Yes[/TD]
[TD]7-72080-86500[/TD]
[TD="align: right"]3236119008[/TD]
[TD="align: right"]8030491954[/TD]
[/TR]
</tbody>[/TABLE]

 

[RickXL]

Hi,

It sounds like you need to find the largest invoice number after a certain date. Would that work?

If so, then you could increment that.

Another approach would be to use another Worksheet that has the next number to use stored in it. You could hide that sheet if being visible was a problem.


Regards,

 

[Chelsea0270516]

Hi Rick! The second approach is something I could do! I played with it a little & have the code below which works! I added in another invoice number (that doesn't change) so it is inputing two invoices now.

If anyone comes up with a cleaner way to do it (Or could explain a better way to write what I have below) I'd love to hear it.

Sub NextInvANDInvSum()

'inserts Inv Sum
'I want to make something that will remember what I previously used - redefine the variable


Dim Inv As Double
Dim SumInv As Double

Inv = ActiveWorkbook.Sheets("Sheet1").Range("A1")
SumInv = ActiveWorkbook.Sheets("Sheet1").Range("A2")

ActiveCell.Offset(1, 0).Range("A1:H1").Select
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = Inv
ActiveCell.Offset(1, 0).Range("A1:I1").Select
ActiveCell.Offset(-1, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = SumInv

ActiveWorkbook.Sheets("Sheet1").Range("A1") = Inv + 1



End Sub