Runtime error 6 overflow

[tezza]

Hi

I get a runtime error 6 overflow when I run the macro on a large spreadsheet of 44000 lines but it works fine on something like 15000 lines.

What can I do to allow it to run successfully.

The code stops at

For n = 2 To No_rows

Set s1 = Worksheets("Sheet1")
No_rows = s1.Cells(Rows.Count, 2).End(xlUp).Row
n = 2
    For n = 2 To No_rows
        Basic = Basic_Pay(s1.Cells(n, 19), s1.Cells(n, 9), s1.Cells(n, 10), s1.Cells(n, 12), s1.Cells(n, 13))
        s1.Cells(n, 20) = Basic
        
        Dom = Dom_care(s1.Cells(n, 19), s1.Cells(n, 9), s1.Cells(n, 10), s1.Cells(n, 12), s1.Cells(n, 13))
        s1.Cells(n, 21) = Dom
        
        Special_Task = Sp_Task(s1.Cells(n, 19), s1.Cells(n, 9), s1.Cells(n, 10), s1.Cells(n, 12), s1.Cells(n, 13))
        s1.Cells(n, 22) = Special_Task
        
        Weekends = Weekend(s1.Cells(n, 19), s1.Cells(n, 9), s1.Cells(n, 10), s1.Cells(n, 12), s1.Cells(n, 13))
        s1.Cells(n, 23) = Weekends
        
        Offices = Office(s1.Cells(n, 19), s1.Cells(n, 9), s1.Cells(n, 10), s1.Cells(n, 12), s1.Cells(n, 13))
        s1.Cells(n, 24) = Offices
    Next n

 

[smozgur]

It should be the variable type problem. Make sure the No_rows variable is defined as a Long type.

 

[Peter_SSs]

Is n declared as Integer?
If so, change that to Long

 

[smozgur]

Is n declared as Integer?
If so, change that to Long

Ah! I was too focused on the No_rows, but the code fails on the loop start, so the No_rows should be already declared as long. You are right, Peter

 

[tezza]

Is n declared as Integer?
If so, change that to Long

Great, that worked.

But now it stops here:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    SrcData, Version:=6).CreatePivotTable TableDestination:= _
    "Hours!R1C1", TableName:="Staff_Hours", DefaultVersion:=6

Within this code

Sheets("Sheet1").Select
'ScrData =
SrcData = ActiveSheet.Name & "!" & Range("A1", Range("A1").SpecialCells(xlLastCell)).Address(ReferenceStyle:=xlR1C1)
Sheets.Add(After:=ActiveSheet).Name = "Hours"
Sheets("Hours").Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    SrcData, Version:=6).CreatePivotTable TableDestination:= _
    "Hours!R1C1", TableName:="Staff_Hours", DefaultVersion:=6
Set pvt = ActiveSheet.PivotTables("Staff_Hours")
'Add item to filter
pvt.PivotFields("Staff Staff Pay Group").Orientation = xlPageField
pvt.PivotFields("Chargeable Rate Sheet").Orientation = xlPageField
'Add item to the Row Labels
    pvt.PivotFields("Staff First Name Staff Last Name").Orientation = xlRowField
'Add items to Values
pfName = "Hours"
    pvt.AddDataField pvt.PivotFields("Rounded"), pfName, xlSum
pfName = "BasicP"
    pvt.AddDataField pvt.PivotFields("Basic"), pfName, xlSum
pfName = "DomP"
    pvt.AddDataField pvt.PivotFields("Dom_care"), pfName, xlSum
pfName = "Spt"
    pvt.AddDataField pvt.PivotFields("Specialist Task"), pfName, xlSum
pfName = "WeekendP"
    pvt.AddDataField pvt.PivotFields("Weekend"), pfName, xlSum
pfName = "OfficeP"
    pvt.AddDataField pvt.PivotFields("Office"), pfName, xlSum
pfName = "On-Call"
    pvt.AddDataField pvt.PivotFields("DC/On-Call"), pfName, xlSum
pfName = "TT"
    pvt.AddDataField pvt.PivotFields("Travel Time"), pfName, xlSum
pvt.CalculatedFields.Add Name:="All Staff Mileage", Formula:="=Staff Mileage + Community Mileage"
pfName = "All_staff_mileage"
    pvt.AddDataField pvt.PivotFields("All Staff Mileage"), pfName, xlSum

Image attached of error

 

[Peter_SSs]

That looks like nothing to do with the original question so I suggest that you start a new thread for this new question.
You should also provide more detail about what "now it stops here" actually means and also about your data.