Finding the next sequence number in vba

Question

I am creating a button that can automatically enter a number in order. So on cell "A1" the cell is the title and it is Title Number. "A2" is blank. I would like it, that if I click on my button, A2 would be 1 and when I click on it again, A3 would be 2 and so on. I am trying to apply this to my userform and I am stuck on this part. What code can I use for this?

Thank you

Private Sub Save_Click()
Dim ws as Worksheet: Set ws = Thisworkbook.ActiveSheet
Dim LastRow as Long
Dim title as Long

title = ws.Range("A:A").Find("Title Number").Row
LastRow = ws.Range("A:A").Find(What:="", After:=Cells(title,1)).Row

I am stuck at this part, I do not know how to start it off after this. What I am trying to do is that whatever number that is, I would want to reference that number so I can input in more data to that specific number row.

Hope I am making sense here.

[This](https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-a-specific-column) may be useful, once you have the number of the last row, it's easy to get the value of the cell in column `A`, add `1` to that value and put it in teh cell below — cybernetic.nomad, Oct 17 '19 at 20:24

score 2 · Accepted Answer · answered Oct 17 '19 at 20:25

You can do something like this:

Sub test()
    Dim ws As Worksheet
    Dim f As Range, nums As Range

    Set ws = ThisWorkbook.ActiveSheet

    'find the header
    Set f = ws.Range("A:A").Find("Title Number", lookat:=xlWhole, LookIn:=xlValues)

    If Not f Is Nothing Then
        'find the occupied range below the header
        Set nums = ws.Range(f.Offset(1, 0), ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0))
        'populate max. value plus one
        nums.Cells(nums.Cells.Count).Value = Application.Max(nums) + 1
    Else
        MsgBox "No Title Number header!"
    End If

End Sub

Finding the next sequence number in vba

1 Answers1