Item number with current month

[Honhkonger]

Hi! I want to make some item number with current month
For example
today is November
so cell A1 = 1/11
When insert a new row, cell A2=2/11,A3=3/11 etc.

when the beginning of December
the number reset to 1 with month when insert a new row i.e 1/12, 2/12, 3/12


Can do this? Thanks

[SamT]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A) is Nothing Then _
       CheckMonthOfItem Intersect(Target, Range"A:A")
End Sub

Private Sub CheckMonthOfItem(Rng As Range)
Dim Cel As Range

Application.EnableEvents = False
   For Each Cel in Rng
     If Cel.Address = "A1" Then 
       Cel = "'1/" & Month(Date) 'Special Case

On Error GoTo CelNext
     ElseIf CInt(Split(Cel.Offset(-1), "/")(1)) = Month(Date) Then    
       Cel = CStr(CLng(Split(Cel.Offset(-1), "/")(0) + 1) & "/" & Month(Date)) 'Typical
     ElseIf  CInt(Split(Cel.Offset(-1), "/")(1) = Month(DateAdd( "m", -1, Date) Then 
       Cel = "'1/" & Month(Date) 'New Month
     End If
CelNext: Err = 0
   Next Cel
Application.EnableEvents = True
End Sub

[arnelgp]

same thing:

Private Sub Worksheet_Change(ByVal Target As Range)Dim LastRow As Long, n As Integer, m As Integer, mm As Integer, va As String
Dim sMn As String
Static bHandled As Boolean
sMn = Format$(Date, "mm")
m = Month(Date)
If Not bHandled Then
    bHandled = True
    On Error Resume Next
    If Left$(Target.Address, 2) = "$A" Then Exit Sub
    If Len(Target & "") = 0 Then
        Target.Offset(, -1).ClearContents
        Exit Sub
    End If
    With Target.Parent
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        va = Trim$(.Cells(LastRow, 1) & "")
        If Len(va) = 0 Or IsDate(va) = False Then
            Range("A" & Target.Row) = "'1/" & Month(Date) & ""
        Else
            n = Val(Split(va, "/")(0))
            mm = Val(Split(va, "/")(1))
            If m = mm Then
                Range("A" & Target.Row) = "'" & n + 1 & "/" & m & ""
            Else
                Range("A" & Target.Row) = "'1/" & Month(Date) & ""
            End If
        End If
    End With
Else
    bHandled = False
End If
End Sub

[SamT]

@ arnelgp,
?
If Not bHandled then bHandled = True...Else bHandled = False?
So... Ignore every even numbered change in Column "A"?

Why, if there is a value is deleted in any other column, Clear the contents of the Cell to the left of the Changed Cell?

IsDate(va)? What kind of Date is "999999/11"?
For that matter, what kind of Integer is 999999?
(I do like adding the Apostrophe prefix to the number String in the cell. I think I'll my Post.)

Or do you know something about Honhkonger's Workflow that the rest of us don't?


BTW: Row.Insert will not trigger a Worksheet_Change on all versions of Office.