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
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