Command button that generates sequential numbers in column A

[Teleporpoise]

Hello Friends,

As the title says, I want to create a command button that when clicked, will generate a sequential number starting from 1. This is what I have coded for it

Private Sub CommandButton1_Click()
    If CurrentRow = 5 Then
        ThisWorkbook.Worksheets("FR").Cells(CurrentRow, "A").Value = 1
    ElseIf CurrentRow > 5 Then
        ThisWorkbook.Worksheets("FR").Cells(CurrentRow, "A").Value = 1 + ThisWorkbook.Worksheets("FR").Cells(CurrentRow, "A").Value
    End If
End Sub

But nothing happens when I click the button. What do I do?

Thanks!

P.S. I am interested in how to reset the button back to 1. To clarify, the purpose is to have a serial number for components being processed, but I want the column A to start from 1 again if I choose a different component. Any hints to the direction I need to go?

 

[n1lesh]

try this assuming you already have numbers in column A

Private Sub CommandButton1_Click()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & Lastrow + 1) = Range("A" & Lastrow) + 1

End Sub
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & Lastrow + 1) = Range("A" & Lastrow) + 1

 

[n1lesh]

try this assuming you already have numbers in column A

Private Sub CommandButton1_Click()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & Lastrow + 1) = Range("A" & Lastrow) + 1

End Sub

 

[Teleporpoise]

Hi N1lesh, Thank you for your response. I tried putting number 1 in the cell above but the button is not generating the numbers

 

[n1lesh]

can you display what your spread sheet looks like

 

[Teleporpoise]

Here is a link to an image: https://imgur.com/a/Ev6CUei

I'm sorry I couldn't upload it for some reason.

 

[Snakehips]

Teleporpoise,

The reason your code is doing nothing is because your variable CurrentRow has not been assigned a value and is therefore empty. Thus your if statement fails on both counts.

Try the below and report back.

Private Sub CommandButton1_Click()

[COLOR=#0000ff]CurrentRow = ActiveCell.Row[/COLOR]


    If CurrentRow = 5 Then
        ThisWorkbook.Worksheets("FR").Cells(CurrentRow, "A").Value = 1
    ElseIf ActiveCell.Row > 5 Then
        ThisWorkbook.Worksheets("FR").Cells(CurrentRow, "A").Value = 1 + ThisWorkbook.Worksheets("FR").Cells(CurrentRow, "A").Value
    End If
End Sub

Hope that helps.

 

[alansidman]

This is a VBA that offers you some variable options as to column, number of rows and which rows to start from as well as your starting number. This is the one I use when needed.

Sub AutoNumberFill()
    Dim x As Long
    Dim rn As Long
    Dim cn As String, sn As Long
    cn = InputBox("Which Column to fill?")
    
    
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Which row to start fill?")


    Application.ScreenUpdating = False
    Application.StatusBar = "Macro Running"
    x = InputBox("What is the starting number?")
    Range(cn & sn).Select
    Do Until ActiveCell.Row = rn + sn
        If ActiveCell.EntireRow.Hidden = False Then
            ActiveCell.Value = x
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub

 

[Teleporpoise]

That worked great, thank you!