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

lyna91

New Member
Joined
Dec 16, 2018
Messages
21
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hello,

It seems from your description that the main criteria is the Weekday ...

Where is it located ...?

Meanwhile ... for the fun of it ... you could test in cell J2 the following formula :

Code:
=COUNTIF(INDIRECT("H"&(INT(INT(ROW()-3)/5)+1)*(INT(INT(ROW()-3)/5)+3)):H5,H5)

Hope this will help
 
Upvote 0
Hello,

It seems from your description that the main criteria is the Weekday ...

Where is it located ...?

Meanwhile ... for the fun of it ... you could test in cell J2 the following formula :

Code:
=COUNTIF(INDIRECT("H"&(INT(INT(ROW()-3)/5)+1)*(INT(INT(ROW()-3)/5)+3)):H5,H5)

Hope this will help


Hi James.

The weekday numbering is located in sheet2 column B9.


The code below is to restart on monday:-


Sub restart()
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$" & .Row & ":h" & .Row & ",h" & .Row & "),5)+" & _
"if(mod(countif(h$" & .Row & ":h" & .Row & ",h" & .Row & "),5)=0,5,0)"
.Value = .Value
End With
End Sub

If only I have a code that can continue from the last count after restarting.
 
Upvote 0
but I havent tied it yet to restart when sheet2.Range("b9").value = Empty trying to figure out the continuation number after restart first .
 
Upvote 0
Thanks for the info ...

Meanwhile have you tested the formula ... ?

Does it produce your expected results ...?
 
Upvote 0
Could you make a copy of your file ... remove all irrelevant AND confidential data ...

And ... then... upload it to Dropbox or Google Drive ...?
 
Upvote 0
This is my other failed attempt as well:-
Sub testmonyet()

Dim lastRow, CustRow, ZoomLev As Long
Dim b, q, h As String

lastRow = Sheet1.Range("E9999").End(xlUp).Row 'Last Row
For CustRow = 5 To lastRow

b = Sheet2.Range("B9").Value
q = Sheet1.Range("Q" & CustRow).Value


If b = 1 And q = Empty Then


Call restart

Else:
IsEmpty(q) = False
If q = Empty And b > 1 Then

With Range("h5", Range("h" & Rows.count).End(xlUp)).Columns(2)
.Formula = "=mod(countif(h$5:h5,h5),5)+if(mod(countif(h$5:h5,h5),5)=0,5,0)"
.Value = .Value
End With

End With
End If
End If
Next CustRow
End
End
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top