Private Sub CommandButton1_Click()
i = 2
j = 2
Do While Not Sheet1.Cells(i, j) = ""
yy = Year(Sheet1.Cells(i, 3))
mm = Month(Sheet1.Cells(i, 3))
If yy < prevyear Then
k = 0
prevyear = yy
End If
k = k + 1
Sheet1.Cells(i, 5) = "A" & Right((yy), 2) & Format(mm, "00") &
Format(k, "00")
i = i + 1
Loop
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"angel" wrote in message
...
what condition can i add to this source code in order for the counter to
reset to 1 as the year changes? i used a macro and vb editor in excel.
Private Sub CommandButton1_Click()
i = 2
j = 2
Do While Not Sheet1.Cells(i, j) = ""
yy = Year(Sheet1.Cells(i, 3))
mm = Month(Sheet1.Cells(i, 3))
k = k + 1
Sheet1.Cells(i, 5) = "A" & Right((yy), 2) & Format(mm, "00") & Format(k,
"00")
i = i + 1
loop
end sub

ex. date is 05/03/95, output should be A950501
06/04/95, output should be A950602
03/03/96, output should be A960301