Add next sequence

[WMYeo]

Can anyone help me how to make it auto fill base on last code either number or alphabet.

6B454A5Z Sample code

The next will be 6B454A6A ,6B454A6B and so until6B454A6Z then change letter to6B454A7A

---

If the last code is number i will auto increase also Example as below 6B454A6

The next will be 6B454A6 ,6B454A7 and so until6B454A8 then change letter to6B454A9

 

[baitmaster]

Define "autofilll"

There's an existing feature called autofill, which will increment numbers for you but won't increment the letters.

You can create custom lists so that when you drag them they cycle through the list - a built in example is jan, feb, mar etc. But this is a precise list of fixed length and won't work with the numbers. We might be able to take advantage of the feature though.

Your best bet would be VBA based. Which brings me back to how you want this to work, because VBA will need to be triggered in a certain way in order to run. So tell me, assuming you enter your value in say cell A1, how would we know where to fill to?

I suggest a piece of code where you select a range of cells, and it increments as you want from the first cell - or perhaps any individual cell containing data in that range. If multiple columns are selected it would run down each column one at a time. It would fill EVERY cell selected, so if you accidentally selected a whole column it would write in every cell: a million+ cells. So I'd advise a warning in case many cells are selected - what sort of quantity would be expected?

 

[baitmaster]

Option Explicit
Option Compare Text
    
Const strAlphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZA"
    
Sub incrementLettersAndNumbers()
Dim iStrLength As Integer, iCharacter As Integer, iLoop As Integer
Dim cl As Range, strThis As String, i As Integer
Dim arrText() As String
    
For Each cl In Selection
    
    ' set new string if it exists
    If cl <> "" Then
        strThis = cl.Value          ' set next text string to work with
        iStrLength = Len(strThis)   ' get text length
        iCharacter = iStrLength     ' start with last character
        
        ' load text strings into an array
        ReDim arrText(1 To iStrLength) As String
        For i = 1 To iStrLength
            arrText(i) = Mid(strThis, i, 1)
        Next i
        
        ' don't increment this cell
        GoTo nextCL
    End If
    
    ' attempt to increment non-blanks
    If strThis <> "" Then
        
        ' attempt to increment every text character
        For iLoop = iStrLength To 1 Step -1
            arrText(iLoop) = incrementCharacter(arrText(iLoop)) ' increment next character
            If arrText(iLoop) <> "A" And arrText(iLoop) <> "0" Then Exit For ' escape process if character didn't "loop" i.e. Z to A or 9 to 0
        Next iLoop
        
        ' write results back to cell
        cl = Join(arrText, "")
    End If
nextCL:
Next cl
    
End Sub
    
Function incrementCharacter(str As String) As String
' this function uplifts numbers and letters. ANY other character becomes #
Dim iStr As Integer: iStr = InStr(1, strAlphabet, str)
If iStr > 0 Then
    incrementCharacter = Mid(strAlphabet, iStr + 1, 1)
ElseIf IsNumeric(str) Then
    incrementCharacter = Right(str + 1, 1)
Else
    incrementCharacter = "#"
End If
End Function

 

[WMYeo]

Option Explicit
Option Compare Text
    
Const strAlphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZA"
    
Sub incrementLettersAndNumbers()
Dim iStrLength As Integer, iCharacter As Integer, iLoop As Integer
Dim cl As Range, strThis As String, i As Integer
Dim arrText() As String
    
For Each cl In Selection
    
    ' set new string if it exists
    If cl <> "" Then
        strThis = cl.Value          ' set next text string to work with
        iStrLength = Len(strThis)   ' get text length
        iCharacter = iStrLength     ' start with last character
        
        ' load text strings into an array
        ReDim arrText(1 To iStrLength) As String
        For i = 1 To iStrLength
            arrText(i) = Mid(strThis, i, 1)
        Next i
        
        ' don't increment this cell
        GoTo nextCL
    End If
    
    ' attempt to increment non-blanks
    If strThis <> "" Then
        
        ' attempt to increment every text character
        For iLoop = iStrLength To 1 Step -1
            arrText(iLoop) = incrementCharacter(arrText(iLoop)) ' increment next character
            If arrText(iLoop) <> "A" And arrText(iLoop) <> "0" Then Exit For ' escape process if character didn't "loop" i.e. Z to A or 9 to 0
        Next iLoop
        
        ' write results back to cell
        cl = Join(arrText, "")
    End If
nextCL:
Next cl
    
End Sub
    
Function incrementCharacter(str As String) As String
' this function uplifts numbers and letters. ANY other character becomes #
Dim iStr As Integer: iStr = InStr(1, strAlphabet, str)
If iStr > 0 Then
    incrementCharacter = Mid(strAlphabet, iStr + 1, 1)
ElseIf IsNumeric(str) Then
    incrementCharacter = Right(str + 1, 1)
Else
    incrementCharacter = "#"
End If
End Function

I hvt try it yet by the ways thank for helping me baitmaster