Function LetterIncrement(stringCounter As String) As String
Dim arr(1 To 4) As Integer
Dim i As Integer
For i = 1 To 4
arr(i) = Asc(UCase(Mid(stringCounter, i, 1)))
Next
arr(4) = arr(4) + 1
For i = 4 To 1 Step -1
If (arr(i) > 90) Then
arr(i) = 65
arr(i - 1) = arr(i - 1) + 1
End If
Next
LetterIncrement = Chr(arr(1)) & Chr(arr(2)) & Chr(arr(3)) & Chr(arr(4))
End Function
=CHAR(MOD(INT((MAX((IFERROR(CODE(MID($G2:$G$1000,5,1))-65,0))*26^3+(IFERROR(CODE(MID($G2:$G$1000,6,1))-65,0))*26^2+(IFERROR(CODE(MID($G2:$G$1000,7,1))-65,0))*26+IFERROR(CODE(MID($G2:$G$1000,8,1))-65,0))+1)/26^3),26)+65)&CHAR(MOD(INT((MAX((IFERROR(CODE(MID($G2:$G$1000,5,1))-65,0))*26^3+(IFERROR(CODE(MID($G2:$G$1000,6,1))-65,0))*26^2+(IFERROR(CODE(MID($G2:$G$1000,7,1))-65,0))*26+IFERROR(CODE(MID($G2:$G$1000,8,1))-65,0))+1)/26^2),26)+65)&CHAR(MOD(INT((MAX((IFERROR(CODE(MID($G2:$G$1000,5,1))-65,0))*26^3+(IFERROR(CODE(MID($G2:$G$1000,6,1))-65,0))*26^2+(IFERROR(CODE(MID($G2:$G$1000,7,1))-65,0))*26+IFERROR(CODE(MID($G2:$G$1000,8,1))-65,0))+1)/26),26)+65)&CHAR(MOD((MAX((IFERROR(CODE(MID($G2:$G$1000,5,1))-65,0))*26^3+(IFERROR(CODE(MID($G2:$G$1000,6,1))-65,0))*26^2+(IFERROR(CODE(MID($G2:$G$1000,7,1))-65,0))*26+IFERROR(CODE(MID($G2:$G$1000,8,1))-65,0))+1),26)+65)
Private Function Base26Max(rng As Range) As String
Dim r As Range
Dim i As Long, j As Long, currMax As Long
Dim arr(1 To 4) As Integer
Dim str As String
currMax = 0
For Each r In rng
str = Right(r.Value, 4)
j = 0
For i = 1 To 4
j = j + ((Asc(UCase(Mid(str, i, 1))) - 65) * (26 ^ (i - 1)))
Next
If j > currMax Then Base26Max = str
Next
End Function
Function LetterIncremented(rng As Range) As String
Dim arr(1 To 4) As Integer
Dim i As Integer
Dim str As String
str = Base26Max(rng)
For i = 1 To 4
arr(i) = Asc(UCase(Mid(str, i, 1)))
Next
arr(4) = arr(4) + 1
For i = 4 To 1 Step -1
If (arr(i) > 90) Then
arr(i) = 65
arr(i - 1) = arr(i - 1) + 1
End If
Next
If arr(1) = 65 Then
If arr(2) = 65 Then
If arr(3) = 65 Then
LetterIncremented = Chr(arr(4))
Else
LetterIncremented = Chr(arr(3)) & Chr(arr(4))
End If
Else
LetterIncremented = Chr(arr(2)) & Chr(arr(3)) & Chr(arr(4))
End If
Else
LetterIncremented = Chr(arr(1)) & Chr(arr(2)) & Chr(arr(3)) & Chr(arr(4))
End If
End Function
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.