VBA overflow problem, export data

[FLYERR]

Dear all, I have the following code and expect to have 80000 rows in the output spreadsheet, but job stops at 30000 rows and saying overflow. How can I export the output into multiple spreadsheets like: 3 or 4 sheets? I mean, when the first sheet reach 30000 or 200 count loop, it goes to the second sheet. :banghead:

Thank you!!

Option Explicit
Sub Run_All_Loans()
Dim counter As Integer
Dim Loan_Number As String
Worksheets("output").Range("A1:ZZ800000").ClearContents
Worksheets("output").Range("A1:BB3").Value _
= Worksheets("Calculation Flow").Range("B3:BC3").Value
For counter = 2 To 500
Loan_Number = Worksheets("loan_input").Cells(counter, 1).Value
Worksheets("Calculation Flow").Range("Loan_No_Input").Value = Loan_Number
Worksheets("Calculation Flow").Calculate
Worksheets("Calculation Flow").Calculate
Worksheets("output").Range("A2:BB82").Offset(81 * (counter - 2), 0).Value = Worksheets("Calculation Flow").Range("B4:BC84").Value
Next counter
End Sub

[Paul_Hossler]

Try this ...


Option Explicit
Sub Run_All_Loans()
Dim counter As Long, temp As Long ' changed to Long
Dim Loan_Number As String
Worksheets("output").Range("A1:ZZ800000").ClearContents
Worksheets("output").Range("A1:BB3").Value = Worksheets("Calculation Flow").Range("B3:BC3").Value
For counter = 2 To 500

Loan_Number = Worksheets("loan_input").Cells(counter, 1).Value
Worksheets("Calculation Flow").Range("Loan_No_Input").Value = Loan_Number
Worksheets("Calculation Flow").Calculate
' Worksheets("Calculation Flow").Calculate why twice???

temp = 81 * (counter - 2)

Worksheets("output").Range("A2:BB82").Offset(temp, 0).Value = Worksheets("Calculation Flow").Range("B4:BC84").Value
Next counter
End Sub


or possibly


Worksheets("output").Range("A2:BB82").Offset(Clng(81) * (counter - 2), 0).Value = Worksheets("Calculation Flow").Range("B4:BC84").Value


Paul

[FLYERR]

Thank you very much!! Sir! This works.
I used twice calculation since excel has forced calculation to manual for some reason at some point. By using twice calculation solved the problems.
:beerchug:


Try this ...


Option Explicit
Sub Run_All_Loans()
Dim counter As Long, temp As Long ' changed to Long
Dim Loan_Number As String
Worksheets("output").Range("A1:ZZ800000").ClearContents
Worksheets("output").Range("A1:BB3").Value = Worksheets("Calculation Flow").Range("B3:BC3").Value
For counter = 2 To 500

Loan_Number = Worksheets("loan_input").Cells(counter, 1).Value
Worksheets("Calculation Flow").Range("Loan_No_Input").Value = Loan_Number
Worksheets("Calculation Flow").Calculate
' Worksheets("Calculation Flow").Calculate why twice???

temp = 81 * (counter - 2)

Worksheets("output").Range("A2:BB82").Offset(temp, 0).Value = Worksheets("Calculation Flow").Range("B4:BC84").Value
Next counter
End Sub


or possibly


Worksheets("output").Range("A2:BB82").Offset(Clng(81) * (counter - 2), 0).Value = Worksheets("Calculation Flow").Range("B4:BC84").Value


Paul

[Paul_Hossler]

No problem

Mark it 'Solved" using Thread Tools at the top of Post #1

Stop by if you have another question or can answer some one else's question

Paul