[Solved] EXCEL VBA Code to generate serial numbers from a range [closed]

by


Try this out and let me know if it works for you.. This will provide you with the results shown in your picture. 

    Sub SerialCode()
    Dim ws As Worksheet
    Dim startOf As Long, endOf As Long, data 'startOf and endOf are Long for >5 digits.
    Dim lastRow As Long, currentRow As Long
    Dim dataRange As Range, c As Range

    Set ws = ThisWorkbook.Sheets(1)
    ws.Range("E2", ws.Cells(ws.Rows.Count, "E").End(xlUp)).Clear
    ws.Range("F2", ws.Cells(ws.Rows.Count, "F").End(xlUp)).Clear
    lastRow = 2
    Set dataRange = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))


    For Each c In dataRange
        If c.Value <> "" Then
            currentRow = c.Row
            startOf = CLng(ws.Range("A" & currentRow).Value)
            endOf = CLng(ws.Range("B" & currentRow).Value) + 1

            If endOf <> 1 And startOf < endOf Then 'This will stop Overflow errors from occuring accidently
                data = ws.Range("C" & currentRow).Value

                Do While startOf <> endOf
                    ws.Range("E" & lastRow).Value = startOf
                    ws.Range("F" & lastRow).Value = data
                    startOf = startOf + 1
                    lastRow = lastRow + 1
                Loop

            Else: MsgBox "Ending Serial Number missing or is less than starting Serial Number on row " _
                & c.Row, vbCritical, "Missing Data"
            End If
        End If
    Next c
End Sub

17

solved EXCEL VBA Code to generate serial numbers from a range [closed]