How to increment Alpha numeric Value

[simora]

I have 2 columns A & B

In column A1 I have a 4 digit number like 5552

I want to increment the number in Column A so that it increments every time a number is entered adjacent in Column B so that Column A shows like

5552-01
5552-02
5552-03

This works if its a whole number, but I can't get it to increment the series like I want.



If Target.Row > 1 Then
Target.Offset(1, -1).Value = Target.Offset(0, -1).Value + 1
End If


Any code & ideas appreciated.

[GTO]

I would suggest attaching a small workbook with what you have and what the expected results should be.

[simora]

Thanks for the response:

What we decided was to simply enter the root number and let the operator enter the last 2 digits so that entries could be made OUT OF SEQUENCE which may be desirable in some cases.
This code accomplishes this.


Dim str As String

If Target.Row > 1 Then
Target.Offset(1, -1).Value = Left(str, 5)
End If


This solves my problem, however, I would love to know how to programically increment this bit of code by 1 =Right(str, 2)

Thanks

[simora]

GTO:

Sorry: I Answered out of sequence.
The expected result was as stated in the original question.
We needed Column A to show like

5552-01
5552-02
5552-03

I also forgot this bit of code on top.


str = Target.Offset(, -1).Value

By using Left(str, 5) I can keep the root, so how could I code to increment the last 2 digits.
Thanks for your response.

[simora]

GTO:

Sorry: I Answered out of sequence.
The expected result was as stated in the original question.
We needed Column A to show like

5552-01
5552-02
5552-03

I also forgot this bit of code on top.


str = Target.Offset(, -1).Value

By using Left(str, 5) I can keep the root, so how could I code to increment the last 2 digits.
Thanks for your response.

[simora]

Ok.

After a night's sleep, FOR THOSE THAT MIGHT BE INTERESTED, I came up with the code to satisfy the original question.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String, strLeft As String
Dim newValue

If Target.Row > 1 Then
newValue = Right(str, 2) + 1
If Len(newValue) < 2 Then
newValue = 0 & newValue
Else: newValue = newValue
End If

Target.Offset(1, -1).Value = Left(str, 5) & newValue

End If
End If

[simora]

OOOOPS! Forgot this code again at the top.

str = Target.Offset(, -1).Value