Copy the cell above and increment value by 1 (Value is alphabetical)-CodePudding

I'm new to VBA and could use some help. From the active cell, I need to copy the value from the cell above and increment the value by 1. The value is always two letters such as AA, AB, AC. I can find information on incrementing numbers but not letters.

CodePudding user response：

Replace(Range(cellValueHere & "1").Offset(0,1).Address(0,0),"1","")

should work for any two-letter combo (but "ZZ" will give you "AAA")

CodePudding user response：

Byte array approach as start help

For the sake of the art another approach to get a following letter combination by incrementing the numeric value of the last letter in a byte array - this post only demonstrates an alternative method how to procede (c.f. caveat note below):

Public Function IncLetter(Optional ByVal s As String = "AA") As String
'a) get numeric character values
    Dim by() As Byte: by = s
'b) increment last alphabetic letter
    Dim indx As Long: indx = UBound(by) - 1
    by(indx) = by(indx)   1
'c) return new string as function result
    IncLetter = by
End Function

Caveat Increments only the last (2nd letter).

Note: As the above code is only intended as starting idea, you will have to refine the function in order to increment the preceding letter sibling(s),too if the following letter value is higher than Z or z *