Run-Time error 6 Overflow

[hsolanki]

Hello i have got code whereby i get can error overflow on the line

If i = 100 Or i = r.count Then Exit For

and if i change to this

If i = 100 Or i = r.CountLarge Then Exit For

then it works however then it is only copying the first row which is the title only.

can someone help me please

BTW Dim i as long it is variable set as

 

[Joe4]

How is r declared?
What is it?

 

[hsolanki]

Hi Joe Thank you for promote respond. oh sorry r declared as range, below its the complete code

Sub TopNRows()
Dim i As Long
Dim r As Range
Dim rWC As Range

Sheet2.Unprotect Password:="2020"
Sheet3.Unprotect Password:="2020"

Set r = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12)

For Each rWC In r
    i = i + 1
    If i = 100 Or i = r.Count Then Exit For
    Next rWC
  
Range(r(1), rWC).Resize(, 3).SpecialCells(12).Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheet3.Protect Password:="2020"
Sheet2.Protect Password:="2020"
End Sub

 

[Joe4]

Can you post a small sample of data, and explain what exactly it is you are trying to do?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, what is the last row in column A with data?

 

[hsolanki]

Hi Joe, Logit had kindly put all the codes together for me. i have attached an sample book. problem is now that where there is more than 3 or 4 expiry dates for same person error message comes up says overflow and as per my original post when i changed it to r.CountLarge it does not copy the actual data and only copies the title row.

First you check dates and then you can filter it by using the department and if there are any expiry dates with the each department you can be able to send email. password for the userform is: 1234

Sample Training V1 Jan 2021.xlsm

drive.google.com

 

[Fluff]

I would suspect that when you get the error there is only one visible row, hence you get the error.
As this

For Each rWC In r
    i = i + 1
    If i = 100 Or i = r.Count Then Exit For
    Next rWC

is doing absolutely nothing you can get rid of it.If you will always be filtering the data before running that code you can just use

Sub TopNRows()

Sheet2.Unprotect Password:="Bhaji2020"
Sheet3.Unprotect Password:="Bhaji2020"

Sheet2.AutoFilter.Range.Offset(1).Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)

Sheet3.Protect Password:="Bhaji2020"
Sheet2.Protect Password:="Bhaji2020"
End Sub

 

[hsolanki]

Morning. Great it worked however i have got another problem now that for example if theres a only one expiry date and if you select any other department which has no expiry date you can still be able send a blank email is there any way we could avoid this now e.g. if any of the department has got any expiry date when you filtered and comes up with msgbox saying no new expiry date found for and the department name it was filtered.

 

[Fluff]

As that is a totally different question you will need to start a new thread.

 

[hsolanki]

Hi Fluff I have marked this post as resolved and i was wondering if you have time you could help with the post

Still be able to send blank email with no data

Hello I have an workbook whereby it has all the expiry dates and you can filter the each department which is on sheet2 filtered from sheet1 and show you any due dates for the selected department if there are any due dates and can send email based on filtered dates however problem is that...

www.mrexcel.com

Once again Thank you for all the help