Countinue numbering the duplicate list based on the last duplicate list numbering

[lyna91]

Hi everyone, thank you so much for reading this post. Any help on this is truly appreciated.

I have a list starting in column H5 and a VBA that counts the duplicate per week up to 5 and restart the count every monday and the count will be displayed in the column starting from I5. I would like to VBA to be able to continue the last duplicate number

I already have a code but it seems to be not working not sure where I went wrong.

I would like it to do something like this:

Previous Week Friday:-

Column H-----Column I (count)
5.Apple-------1
6.Pear -------1
7.Apple ------2


This week Monday after restarting the numbering:-

Column H-----Column I (count)
8.Apple-------1
9.Pear -------1
10.Apple -----2

This week tuesday
11. Apple-----3
12. Apple-----4


Right now the coding I have instead of countinue the counting in tuesday like the above. It starts to renumbering it back to 1
Below is the code:

Dim LastR As Range, x As Long
If IsEmpty([h5]) Then Exit Sub
Set LastR = [i5]
If Not IsEmpty(LastR) Then Set LastR = Range("i" & Rows.count).End(xlUp)(2)
x = Range("h" & Rows.count).End(xlUp).Row
If x < LastR.Row Then Exit Sub
With Range(LastR, Range("i" & x))
.Formula = "=mod(countif(h$5:h5,h5),5)+if(mod(countif(h$5:h5,h5),5)=0,5,0)"
.Value = .Value
End With
End Sub


Please help me . Thank you so much

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...om-the-last-duplicate-number.html#post5028261

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[lyna91]

Cross posted https://www.excelforum.com/excel-pro...ml#post5028261

 

[James006]

maaf tapi saya tidak mengerti ...