Increment a Sequence vba

Question

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

Assigning FormulaR1C1 seems overkill in this case; you could be assigning the cell's Value directly. That said, with a UDF such as Tim's solution that becomes irrelevant. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 17, 2019 at 22:47
Thank you all for your time and help. I am still new to vba function and all answers gave me something new to learn. — S.camp
– S.camp, Commented Jan 18, 2019 at 15:18

Tim Williams · Accepted Answer · 2019-01-17 22:37:38Z

3

With no error checking:

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

answered Jan 17, 2019 at 22:37

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathieu Guindon Over a year ago

I like how this solution avoids dealing with ActiveCell. Could be used as a UDF!

dwirony Over a year ago

Nice and simple solution!

dwirony · Accepted Answer · 2019-01-17 22:33:53Z

1

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

answered Jan 17, 2019 at 22:33

dwirony

5,4604 gold badges26 silver badges49 bronze badges

1 Comment

Jules Over a year ago

FYI: the case can be replaced with right("0000"&name,4)

Tate Garringer · Accepted Answer · 2019-01-17 22:45:46Z

1

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

edited Jan 17, 2019 at 22:45

answered Jan 17, 2019 at 22:35

Tate Garringer

1,5291 gold badge8 silver badges9 bronze badges

Comments

MBB70 · Accepted Answer · 2019-01-17 22:53:55Z

0

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.

answered Jan 17, 2019 at 22:53

MBB70

3673 silver badges18 bronze badges

Collectives™ on Stack Overflow

Increment a Sequence vba

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related