Error while running macro on Huge rows

[motherindia]

Hello Sir,

The macro will work upto 1.25 lak rows with 110 column, but throws error if it exceeds 1.25 lakh rows with following message


Run time error 7
over flow error.
How could it be fixed



Special thanks to Jindon Sir for providing short code to perform lookup function.
However when I try it on small data it will work. But if number of rows exceed 50k it will error at following line;

a = Sheets("sheet1").Range("a1").CurrentRegion.Resize(, 300).Value


following is the macro used by me.


Sub test()
Dim a, i As Long
a = Sheets("sheet2").Range("a1").CurrentRegion.Resize(, 200).Value
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a, 1)
.Item(a(i, 1)) = a(i, 2)
Next
a = Sheets("sheet1").Range("a1").CurrentRegion.Resize(, 300).Value
a(1, 3) = "Supplier Name"
For i = 2 To UBound(a, 1)
If .exists(a(i, 2)) Then a(i, 250) = .Item(a(i, 2))
Next
End With
Sheets("sheet1").Range("a1").CurrentRegion.Resize(, 250).Value = a
End Sub


Regards,
motherindia