New invoice number

[Jenny809]

Hi there! I am trying to create a code that will generate a new invoice number on my worksheet. I have cell H4 with a vendor number that is four digits long. I would like the invoice number to include the vendor number somehow 5647-78 for example. Could you please help me?

 

[tyija1995]

Hey,

You could do something like:

=H4&"-"&"01"

To get 5647-01 given that H4 = 5647

 

[MickG]

If you place that number in "H4" then drag the cell down by it bottom right corner, the number on the right of "-" will increase by one for every cell.

 

[Jenny809]

Hey,

You could do something like:

=H4&"-"&"01"

To get 5647-01 given that H4 = 5647

Thank you. This is what I have so far but I am sure this is not right

Sub NextInvoice ()
Range (H3).Value = Range (“H4”) &”-“&”01”. Value + 1

End Sub

H3 is where I want the number. H4 has the vendor number

 

[Joe4]

Welcome to the Board!

In order to get an answer that works well for you, it is important to provide as many details as possible.

How exactly do you want the last two numbers to work?
Will each vendor have an "01", "02", "03", etc, or wil there only be one "01"?
It may be necessary to store a counter somewhere, if not look up the provious number.

Will these values always be going in cell H3?

 

[Jenny809]

The values will always be on cell H3. I just want to make sure the two last number change when a new invoice is created. Does that make sense?
Btw, thank you so much for helping me !!!!

 

[Joe4]

The values will always be on cell H3. I just want to make sure the two last number change when a new invoice is created.

In order to do that, the memory of the previous sequence must be stored somewhere.
How do you envision that working?
If the last value is still present in cell H3, we can get that prior value before changing it. Will that work for you?

Also, will the part after the "-" always be two digits long?
If so, you are limited to 100 invoices (numbers 00 - 99).

 

[Joe4]

If my previous assumptions are correct, this would work for any invoice number up to 99:

Sub NewInvoice()

    Dim prevNum As Long
    
'   Get previous invoice number
    prevNum = Mid(Range("H3"), 6)
    
'   Assign new number to cell H4
    Range("H3").Value = Range("H4").Value & "-" & Format(prevNum + 1, "00")
    
End Sub

It may need some slight adjustments, depending on your answer to the last question, how is the invoice number to be formatted.
I.e., what would the Invoice Number look like for 1? How about 10? What about 100?

 

[Jenny809]

I will try that code and see what happens. It doesn’t have to be limited to 100. It is ok if the last invoice number stays in cell H3, that works ?
Thank you so much for you help, I will let you know how it goes

 

[Joe4]

You are welcome.