Increment a number within a string in one cell #VBA

Question

I am trying to match to sheets with same records and update one sheet based on another. The updated goes with and incremental of '1' in respective cell.

It was able to write it so the cells with values will be updated respectively. The problem is that I can't figure it out how to increment a cell that contains a string. (some cells contain ">1", ">2" and so on) I am trying to increment those if needed to change to ">2" and ">3" and so on.

The moment I should paste the code is bolded.

    Sub Increment()
    For Each SnowCell In MySnowRange
    For Each TrakerCell In MyTrakerRange
        If TrakerCell.Value = SnowCell.Value Then
            If TrakerCell.Offset(, 1).Value <> SnowCell.Offset(, 1).Value Then

                TrakerCell.Offset(, 1).Value = SnowCell.Offset(, 1).Value

                Select Case SnowCell.Offset(, 1).Value
                    Case "In Queue"
                        If Application.WorksheetFunction.IsNumber(TrakerCell.Offset(, 3).Value + 1) = True Then
                            TrakerCell.Offset(, 3).Value = TrakerCell.Offset(, 3).Value + 1
                        Else
                            **TrakerCell.Offset(, 3).Value = Split(TrakerCell.Offset(, 3).Value)**
                    Case "Assigned"
                        TrakerCell.Offset(, 4).Value = TrakerCell.Offset(, 4).Value + 1
                    Case "Work In Progress"
                        TrakerCell.Offset(, 5).Value = TrakerCell.Offset(, 5).Value + 1
                    Case "Pending"
                        TrakerCell.Offset(, 6).Value = TrakerCell.Offset(, 6).Value + 1
                    Case "Resolved"
                        TrakerCell.Offset(, 7).Value = "Resolved"
                  End Select

            ElseIf TrakerCell.Offset(, 1).Value = SnowCell.Offset(, 1).Value Then

                Select Case SnowCell.Offset(, 1).Value
                    Case "In Queue"
                        TrakerCell.Offset(, 3).Value = TrakerCell.Offset(, 3).Value + 1
                    Case "Assigned"
                        TrakerCell.Offset(, 4).Value = TrakerCell.Offset(, 4).Value + 1
                    Case "Work In Progress"
                        TrakerCell.Offset(, 5).Value = TrakerCell.Offset(, 5).Value + 1
                    Case "Pending"
                        TrakerCell.Offset(, 6).Value = TrakerCell.Offset(, 6).Value + 1
                    Case "Resolved"
                        TrakerCell.Offset(, 7).Value = "Resolved"
                  End Select

            End If
        Else
        End If
    Next
Next


End Sub

score 0 · Answer 1 · edited May 23 '17 at 11:53

From what I can see, your comparison is working, it's your update that you need to resolve. Write a function that will return you an updated value. What you will need the function to do is first extract the numeric value from the string (have a look at these 2 post on how to do that: Post1 and Post2). Now that you have your numeric value, increment it as per your requirements. Next, replace the numeric value with your new updated value (this should be too hard to do: you have your old value and the new value, just use Replace to change it in the string). You can now use this function to assign the value to your Offset cells

score 0 · Answer 2 · answered Oct 13 '16 at 11:03

Thanks!

Have done it this way and it works:

 Case "In Queue"
                    If   Application.WorksheetFunction.IsNonText(TrakerCell.Offset(, 3).Value) = True Then
                        TrakerCell.Offset(, 3).Value = TrakerCell.Offset(, 3).Value + 1
                    Else

                        a = Left(TrakerCell.Offset(, 3).Value, 1)
                        b = Right(TrakerCell.Offset(, 3).Value, Len(TrakerCell.Offset(, 3).Value) - 1)
                        c = b + 1
                        d = a & c

                        TrakerCell.Offset(, 3).Value = d
                     End If

:)!

score 0 · Answer 3 · answered Oct 13 '16 at 12:22

If you know that there is always only one character before the number, then just:

Set c = TrakerCell(, 4)                   ' same as TrakerCell.Offset(, 3).Resize(1, 1)
c.Value = Left(c, 1) & (Mid(c, 2) + 1)

but if the number of non-numeric characters varies, then it can be changed a bit to:

Set c = TrakerCell(, 4)
L = Len(c) - Len(Str(Val(StrReverse(c)))) + 1 ' number of characters before the number + 1
c.Value = Left(c, L) & (Mid(c, L + 1) + 1)

Increment a number within a string in one cell #VBA

3 Answers3