Incrementing last digit in a custom serial number

[cherrydee]

Hi, i am trying to increment the last digit of a custom field. An example is 2018-08-001. The code i am using is left(a1,10) & 1 + right(a1,1)
I am having issues when it reaches 2018-08-009. It becomes 0010. Also probable future problem is whenb it reaches 2018-08-999.
Any help is appreciated. Thank you!

 

[sandy666]

Enter two first values manually
select yellowcells then drag down

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]2018-08-001[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]2018-08-002[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-003[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-004[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-005[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-006[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-007[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-008[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-009[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-010[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-011[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-012[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-013[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-014[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2018-08-015[/td][/tr]
[/table]

is that what you want?

 

[JoeMo]

If you prefer a formula, this should get you to 2018-08-9999:

Excel Workbook
	A
1	2018-08-001
2	2018-08-002
3	2018-08-003
4	2018-08-004
5	2018-08-005
6	2018-08-006
7	2018-08-007
8	2018-08-008
9	2018-08-009
10	2018-08-010
Test

 

[cherrydee]

Hi, sorry if i wasn't clear. I'm doing this in vba so I can't simply drag down the formula.
Anyway, @joe, i am having an error in the "if section". Is that supposed to be another line of function?

 

[JoeMo]

Hi, sorry if i wasn't clear. I'm doing this in vba so I can't simply drag down the formula.
Anyway, @joe, i am having an error in the "if section". Is that supposed to be another line of function?

Are you using the formula in VBA or directly on the worksheet? If in VBA, post the code and point to the line(s) where you use it. If on the worksheet, did you type it in or copy directly from your browser and paste it?

 

[cherrydee]

I am using my phone to surf the web and i just copied your codes to a note app and typed it in my excel vba since i cant scroll horizontally on your codes.

Dim newvalue as string
Dim value as string
Value="2018-08-009"
Newvalue = left(value,8) & text(mid(value,9,len(value))+1,if(mid(value,9,len(value))+1<=999,"000","0000"))

 

[Rick Rothstein]

If you prefer a formula, this should get you to 2018-08-9999:

Spreadsheet Formulas

Cell Formula A2 =LEFT(A1,8)&TEXT(MID(A1,9,LEN(A1))+1,IF(MID(A1,9,LEN(A1))+1<=999,"000","0000")) <tbody> </tbody>

<tbody>
</tbody>

I know the OP has since said he wants a VB solution although it is not clear how he wants it implemented (how do we know how many?), but I thought I would post a more compact formula for those who might like to see it...

=TEXT(1+SUBSTITUTE(A1,"-",""),"0000-00-000")

This will carry up to at least 999 and, if the next value is 000 rather than 001, then well beyond.

 

[JoeMo]

I am using my phone to surf the web and i just copied your codes to a note app and typed it in my excel vba since i cant scroll horizontally on your codes.

Dim newvalue as string
Dim value as string
Value="2018-08-009"
Newvalue = left(value,8) & text(mid(value,9,len(value))+1,if(mid(value,9,len(value))+1<=999,"000","0000"))

You can't use the worksheet functions TEXT and IF like that in VBA. You would have to qualify them as worksheetfunction.Text(....) and double up the quotes on embedded strings.

Here's an easier way:

Sub IncrementByOne()
Dim StartValue As String, Increment As String, i As Long, V As Variant, NextValue As String
StartValue = "2018-08-001"
V = Split(StartValue, "-")
For i = 0 To 10000
    Increment = V(2) + i
    NextValue = Left(StartValue, 8) & Format(Increment, "000")
    'do something with NextValue. Here I write it to col A
    Range("A1").Offset(i).Value = NextValue
Next i
End Sub

 

[cherrydee]

I know the OP has since said he wants a VB solution although it is not clear how he wants it implemented (how do we know how many?), but I thought I would post a more compact formula for those who might like to see it...

=TEXT(1+SUBSTITUTE(A1,"-",""),"0000-00-000")

This will carry up to at least 999 and, if the next value is 000 rather than 001, then well beyond.

Hi, your code works great! But how can i adjust it when it reaches thousands like 2018-08-1000? Cause I'm not really sure how long the records would be. Thanks!
Btw, i added worksheetfunction before substitute to make it work

 

[cherrydee]

You can't use the worksheet functions TEXT and IF like that in VBA. You would have to qualify them as worksheetfunction.Text(....) and double up the quotes on embedded strings.

Here's an easier way:

Sub IncrementByOne()
Dim StartValue As String, Increment As String, i As Long, V As Variant, NextValue As String
StartValue = "2018-08-001"
V = Split(StartValue, "-")
For i = 0 To 10000
    Increment = V(2) + i
    NextValue = Left(StartValue, 8) & Format(Increment, "000")
    'do something with NextValue. Here I write it to col A
    Range("A1").Offset(i).Value = NextValue
Next i
End Sub

This works like a charm! Tho i removed the for loop since i put the nextvalue in msgbox and it won't stop. Lol. Anyway, not sure what the for loop is for.