help on formula

[angel]

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

 

[Bob Phillips]

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)