Keep adding consecutive number

[Sergio]

Hi all,
Anyone knows the easy way to add a consecutive number in "B" column as soon as data its entering in "C" column?
I have a file that it is use to track open and close cases. Sometimes the cases are closed in a different time frame comparing when the cases were open. We assign a consecutive number so we can keep track of the ticket. When the case is closed we cut the row and move it to another tab inside the excel file. There are plenty of cases and and many cases close that we do not know what is the next consecutive number that need to be assigned to the new case
Any idea how to solve this small issue?
Thanks
Alex

 

[rollis13]

You can use this example of mine as a starting point and customize it according to the structure of your project. You may need to narrow the range that triggers the Worksheet_Change event.
The macro must be pasted into your sheet's module. You will also need to define a helper cell containing the last consecutive number used. In the example I used cell AA1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRowB As Long
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("C:C")) Is Nothing Then 'check for changes in column C
        Range("AA1") = Range("AA1") + 1           'increments helper cell for consecutive number
        LastRowB = Range("B" & Rows.Count).End(xlUp).Row + 1 'find last used cell in column B +1
        Range("B" & LastRowB) = Range("AA1")      'assign new consecutive number to column B
    End If
End Sub

 

[Sergio]

You can use this example of mine as a starting point and customize it according to the structure of your project. You may need to narrow the range that triggers the Worksheet_Change event.
The macro must be pasted into your sheet's module. You will also need to define a helper cell containing the last consecutive number used. In the example I used cell AA1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRowB As Long
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("C:C")) Is Nothing Then 'check for changes in column C
        Range("AA1") = Range("AA1") + 1           'increments helper cell for consecutive number
        LastRowB = Range("B" & Rows.Count).End(xlUp).Row + 1 'find last used cell in column B +1
        Range("B" & LastRowB) = Range("AA1")      'assign new consecutive number to column B
    End If
End Sub

Hi, i will need a little bit more help here... i copied and pasted the macro and trying to work around with the cells, but i can not make it work...
any extra help please?
Thanks
Sergio

 

[rollis13]

First of all, since my example macro is triggered by an event, did you paste it in the affected module as said?
Then if you need other help, since I don't have a crystal ball, how about attaching a copy of your file possibly without sensitive data.

 

[Sergio]

Thank you, I can not upload the excel file here but I tried to do a screenshot of what I did.
Let me explain a little bit more of my problem. In column B I have consecutive number. Item 71 was open and closed the same day and the instructions is to moved the row to the closed item tab, because of doing that the item 70 is the last open item on the file and the next person using the tracker used again the consecutive number 71..
I want just a macro or formula to check both tabs and give me a consecutive number after I enter any data in column "C" that is the date

thanks
Alex

 

[rollis13]

As said in post #2 I suggested a helper cell in AA1 (or other cell of your choice as long as you update the matching ranges in my macro) and valorize it (only once) with the current last number used.