Evaluate cell with alphanumeric

[Nalani]

I am trying to produce an invoice that will increment the invoice #.

This is the Code that I have:

Private Sub CommandButton1_Click()
    Dim Ans As Integer
    Ans = Application.InputBox(Prompt:= _
               "How many copies do you want to print?", Title:="How Many Copies?", _
                Default:=1, Type:=1)
    If Ans = False Then
        Application.DisplayAlerts = False
        Exit Sub
    Else
        ActiveWindow.SelectedSheets.PrintOut Copies:=Ans
    End If
    With ActiveSheet
        .Range("J3").Value = .Range("J3").Value + 1
    End With
End Sub

The incrementing of J3 works fine if it was just numeric.

In my case J3 is alphanumeric.

Cell J3 holds (currently) 100A. This I would like to increment to 101A, etc. And of course one day it may hit 1001A.

Any help appreciated.

Thanks

 

[AlphaFrog]

    With ActiveSheet.Range("J3")
        .Value = Left(.Value, Len(.Value) - 1) + 1 & Right(.Value, 1)
    End With

 

[Nalani]

Thank You Sir

That works as expected.

I'm trying to figure it out. Please correct me where I'm wrong and/or enlighten me.

Len, returns the number of characters in the string.

So: Left(.Value, Len(.Value) - 1) + 1 will get rid of the Alpha character and add 1 to to the numeric. In this case 100A becomes 101

The part that I don't understand is:
& Right(.Value, 1)

How does this equate?

I'm thinking that .Value has now taken on what happened in the first part of the formula ??? Also don't know what the ,1 does (mean).

Thanks

 

[AlphaFrog]

= Left(.Value, Len(.Value) - 1) + 1 & Right(.Value, 1)
= Left(100A, Len(100A) - 1) + 1 & Right(100A, 1)
= Left(100A, 3) + 1 & Right(100A, 1)
= 100 + 1 & Right(100A, 1)
= 101 & A)
= 101A

Right(.Value, 1)
returns the right most 1 character in 100A

 

[Nalani]

Thank you again for the explanation.

Alot going on in that formula, but I at least understand it now.

Have a great night.