VBA to increment hyphenated number

[Lil Stinker]

Does anyone know of a way to increment hyphenated numbers using VBA, a Max formula or any other formula in Excel?
Example: 10000-1 increments to 10000-2 and so on. For my purposes, the first 5 digits do not increment. Only the numbers after the hyphen.

If I put 10000-1 in a cell and drag the handle down, Excel increments the number the way I'd like. However, if I try to use VBA to do the same thing, I get a type mismatch error. If I try to use the Max formula, I get an error.

.Range("A2").Value = .Range("A2").Value + 1 results in the type mismatch error.
.Range("A2").Value = Left(.Range("A2").Value, 6) & 1 + Right(.Range("A2").Value, 1) seems to increment but only up to 10 and then goes back to 1.

I would need the number after the hyphen to increment into the hundreds if possible. TBH, it would be fantastic if the number could be presented as 10000-001 and still increment one digit at a time but everything I've tried only results in the zeroes after the hyphen disappearing.

 

[JoeMo]

TBH, it would be fantastic if the number could be presented as 10000-001 and still increment one digit at a time but everything I've tried only results in the zeroes after the hyphen disappearing.

Try this:

Sub IncrementHyphenatedNumber()
Const startCell As String = "A1"  'change to suit
Const startNum As String = "10000-001"
With Range(startCell)
    .Value = startNum
    .AutoFill .Resize(1000, 1), xlFillSeries
End With
End Sub

 

[Lil Stinker]

Nice! This works great for incrementing in the same column. Is it possible to revise this to have it increment in the same cell (A1)?

I should also clarify because while I don't understand what I'm looking at exactly, it appears startNum is hardcoded to "10000-001".

With this example, 10000 represents the initial receipt number and -001 represents any revisions to that receipt number. While 10000 doesn't increment, it will change based on whatever receipt number is loaded. It could be any number between 10000 & 99999. It all depends on the whichever receipt is loaded into the form.

 

[Peter_SSs]

Is it possible to revise this to have it increment in the same cell (A1)?

Is this the sort of thing that you mean?

Sub IncVal()
  Dim Bits As Variant
  
  With Range("A1")
    Bits = Split(.Value, "-")
    .Value = Bits(0) & Format(Bits(1) + 1, "-000")
  End With
End Sub

So that if A1 starts off with "10000-001" and you run the code the value will become "10000-002"
If A1 started with "555-234" then the code would change it to "555-235"

 

[Lil Stinker]

Is this the sort of thing that you mean?

So that if A1 starts off with "10000-001" and you run the code the value will become "10000-002"
If A1 started with "555-234" then the code would change it to "555-235"

Yes, brilliant! Thanks, Peter, this works perfectly!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.