CODEWITHSUNDEEP
excelvba
S.camp
S.camp

Reputation: 23

Increment a Sequence vba

My sequence will be something like 0000-16x, 0001-16x, 0002-16x, … ,9999-16x I want to write a macro that will read one of the values and increment it by one. e.g. if it reads 0014-16xthe code will return 0015-16x

My code works only if the contents of cell are all numbers 

Dim name As Variant
name = ActiveCell
name = name + 1
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = name

How can I increment a sequence like the one I have? Thanks

Upvotes: 2

Views: 274

Answers (4)

MBB70
MBB70

Reputation: 367

Here is a non-VBA solution, FWIW...

=TEXT(LEFT(A1,FIND("-",A1)-1)+1,"0000")&"-"&MID(A1,FIND("-",A1)+1,99)

with 0000-16x in cell A1 and the formula in cell A2. Simply fill down to extend.

Upvotes: 0

Tate Garringer
Tate Garringer

Reputation: 1529

I can't currently test this since I'm posting from my phone, but you could use the Split() function with a - delimiter and add 1 to the (0) position of the array, join it back together and pad it with 0's until it's length is 8.

Example:

Dim name() As Variant
Dim jName as String
name = Split(ActiveCell,"-")
name(0) = name(0) + 1
jName = Join(name,"-")
Do Until Len(jName) = 8
    jName = "0" & jName
Loop
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = jName

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166940

With no error checking:

Function NextSequence(v)
    Dim arr
    arr = Split(v, "-")
    NextSequence = Format(CLng(arr(0)) + 1, "0000") & "-" & arr(1)
End Function

Upvotes: 3

dwirony
dwirony

Reputation: 5450

Let's split the string by the delimiter of "-", increment the number by 1, append zeroes and reform our string - give this a shot:

Sub Increment()

    Dim name As Variant
    Dim firsthalf As String, secondhalf As String

    name = Split(ActiveCell, "-")(0)
    secondhalf = Split(ActiveCell, "-")(1)
    name = name + 1

    Select Case Len(name)
        Case 1
            firsthalf = "000" & name
        Case 2
            firsthalf = "00" & name
        Case 3
            firsthalf = "0" & name
        Case 4
            firsthalf = name
    End Select

    ActiveCell.Offset(1, 0).Activate
    ActiveCell.FormulaR1C1 = firsthalf & "-" & secondhalf

End Sub

Upvotes: 1

Related Questions