Excel VBA add 1 to right of above cell

SUNRISE95 0
Viewed 41

sorry for this amateur question i'm just a beginner in VBA.

i have a cell that contains 19 characters of numbers. as u know, because of its length excel can't show whole number till u change the cell format to Text.

now the A1 Value is : 3310195401193581830.

if i want to use autofill series, it doesn't work cause the cell format is Text. so i just split the value into two parts as shown below:

Dim SerialIDR, SerialIDL As String
SerialIDR = Right(ThisWorkbook.Worksheets("Sheet1").Range("A1").Value, 9)
SerialIDL = Left(ThisWorkbook.Worksheets("Sheet1").Range("A1").Value, 10)
ThisWorkbook.Worksheets("Sheet1").Range("A2").Value = SerialIDL & SerialIDR + 1

SerialIDR = Right(ThisWorkbook.Worksheets("Sheet1").Range("A1").Value, 9)
SerialIDL = Left(ThisWorkbook.Worksheets("Sheet1").Range("A1").Value, 10)
ThisWorkbook.Worksheets("Sheet1").Range("A3").Value = SerialIDL & SerialIDR + 2

SerialIDR = Right(ThisWorkbook.Worksheets("Sheet1").Range("A1").Value, 9)
SerialIDL = Left(ThisWorkbook.Worksheets("Sheet1").Range("A1").Value, 10)
ThisWorkbook.Worksheets("Sheet1").Range("A4").Value = SerialIDL & SerialIDR + 3

so can someone help me to do this:

get the 19 chars number.loop through a column and autofill series this value for X cells in column. just like this: 3310195401193581830

3310195401193581831

3310195401193581832 ...

vba excel
1 Answers

Autofill Integer String Series

Sub AutoFillIntegerStringSeries()
    
    ' Reference the worksheet ('ws')
    ' in the workbook containing this code ('ThisWorkbook').
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Reference the range ('rg'). Its first cell holds
    ' the initial integer string.
    Dim rg As Range: Set rg = ws.Range("A1:A10")
    
    ' Store the number of rows in a variable ('rCount').
    Dim rCount As Long: rCount = rg.Rows.Count - 1 ' not counting the first cell
    ' Validate the number of rows.
    If rCount = 0 Then Exit Sub ' there is just one row containing the string

    ' Store the initial string in a variable ('CurrentString').
    Dim CurrentString As String: CurrentString = CStr(rg.Cells(1).Value)
    ' Using the 'IsDigits' function, validate the initial string.
    If Not IsDigits(CurrentString) Then Exit Sub ' not all characters are digits
    
    ' Define an array ('Data') to hold the resulting integer strings.
    Dim Data() As String: ReDim Data(1 To rCount, 1 To 1)
    
    ' Declare variables.
    Dim r As Long ' Current Array Row (Element)
    
    ' Loop through the rows of the array.
    For r = 1 To rCount
        ' Using the 'IncrementIntegerString' function on the current string,
        ' store the incremented integer string in the same variable.
        CurrentString = IncrementIntegerString(CurrentString)
        ' Write the incremented string to the current row of the array.
        Data(r, 1) = CurrentString
    Next r

    ' Write the values from the array to the range excluding the first cell.
    rg.Resize(rCount).Offset(1).Value = Data

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a boolean indicating whether all characters of a string
'               are digits.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IsDigits(ByVal s As String) As Boolean
    If Len(s) > 0 Then IsDigits = s Like String(Len(s), "#")
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns an integer string which is by one greater than
'               the given integer string ('IntegerString').
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IncrementIntegerString(ByVal IntegerString As String) As String
    
    Dim iLen As Long: iLen = Len(IntegerString)
    
    Dim Digit As Long
    Dim n As Long
    
    For n = iLen To 1 Step -1
        Digit = CLng(Mid(IntegerString, n, 1))
        If Digit <> 9 Then Exit For
    Next n
    
    If n = 0 Then
        IncrementIntegerString = "1" & String(iLen, "0")
    Else
        IncrementIntegerString = Left(IntegerString, n - 1) _
            & CStr(Digit + 1) & String(iLen - n, "0")
    End If
    
End Function
VBasic20080
Related