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.