Run time error 6 = over flow

Hello experts
The code is working fine in sample data. But when I ran the code in the original data with more than 1,40,000 rows, the code is displaying the above error message. Please tell me how to over come that message and get the same result.
Thank you in advance.

Try

Please Login or Register  to view this content.

As a general rule you can "Dim" all integer variable as "Long" to avoid this problem; but "mea culpa" for the mistake!

To help code faster, working with big data.
Use this coe:

PHP Code: 

Option Explicit
Sub CreateS()
Application.StatusBar = "Create Sales Headings with Data..."

'delete old Sales sheet
If Evaluate("=ISREF('Sales'!A1)") Then Sheets("Sales").Delete

'dupplicate sheet "consolidated" intead of create new sheet
Application.DisplayAlerts = False
Sheets("consolidated").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sales"
Application.DisplayAlerts = True

'with new Sales sheet, delete specific columns
Range("E5:G5,J5,L5,Q5,S5,U5,W5").EntireColumn.Delete

'insert 1 more column P for "rate %"
Columns("P").Insert

'create headers and format in row 1
Rows("2:5").Delete
With Range("A1:U1")
    .Value = Array("Revenue Group", "Shipped Location", "Order no", "GSTIN/UIN", "No.", "Date", "Quantity", _
            "Value", "Goods/Services", "HSN/SAC", "Taxable Value", "IGST", "CGST", "SGST", "CESS", "Rate %", "Location of Recipient", _
            "Supply Type", "Code", "State Code", "Nature of supply")
    .Font.Bold = True
    .EntireColumn.AutoFit
End With

'main code
Dim lr&, i&, j&, rng, gst, f, ary1, ary2
lr = Cells(Rows.Count, "L").End(xlUp).Row
'copy data into array "rng" and "gst", to avoid working directly on sheet, to help code faster
rng = Range("K2:U" & lr).Value: gst = Range("D2:D" & lr).Value
ary1 = Array("URP", "Export", "Cancelled"): ary2 = Array("B2C", "Export", "Cancelled")
For i = 1 To UBound(rng)
    On Error Resume Next
    rng(i, 6) = Round((rng(i, 2) + rng(i, 3) + rng(i, 4)) / rng(i, 1), 2) * 100
    On Error GoTo 0
    With Sheets("State Code")
        Set f = .Range("B2:B38").Find(Trim(rng(i, 7))) ' looking for Location in column B
        If Not f Is Nothing Then ' if found then
            rng(i, 9) = f.Offset(, 1).Value ' get corresponding value in column C
            rng(i, 10) = f.Offset(, 2).Value ' get corresponding value in column D
        Else ' if not found then
            rng(i, 9) = "OT"
            rng(i, 10) = 97
        End If
        rng(i, 11) = "B2B" ' set defaul value is B2B
        For j = 0 To UBound(ary1)
            If gst(i, 1) = ary1(j) Then rng(i, 11) = ary2(j) ' if column D matchs ary1 the returns corresponding item in ary2
        Next
    End With
Next
'paste array back to sheet
Range("K2:U" & lr).Value = rng
End Sub 


"Nil desparandum" !

Learn from Bebo's approach as (from your posting history) much of your processing is very similar to this requirement. And note, (memory-based) array processing is very fast ( - the latter part of Bebo's macro).

Another variation

Please Login or Register  to view this content.

Data from "Consolidate" is read into array "a" and then copied to array "b" which represents the "Sales" data.

Arrays "a" and "b" could be better named which helps to document the macro: I am just being lazy!!!