CODEWITHSUNDEEP
excelvba
Phiking
Phiking

Reputation: 75

Finding the next sequence number in vba

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.

Upvotes: 0

Views: 1262

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

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

Upvotes: 2

Related Questions