Overflow Error Been Replaced with 1004 error

[ExcelHank]

So I have a very large worksheets(400000 + in length 8 variables) that have the price for a stock and i want to organize it into a worksheet where all cells are side by side.

I trouble shooted through until I got it to work for about half of the data set until it said overflow error. I check to see if there was an error listed in one of the price files but there didn't seem to be.

Then when I went to debug it came up with a 1004 error on the line that reads;

matrixdate = WorksheetFunction.Match(datelookup, Range("k3:k4260"), 0)

the entire script is below

Sub proZero()


Sheets("Matrix").Select
Application.Calculation = xlCalculationManual


Dim off As Integer
Dim datelookup As Variant
Dim comlookup As String
Dim matrixcom As Integer
Dim matrixdate As Variant


off = 1


Do While off < 4219912


datelookup = Range("b1").Offset(off, 0).Value
matrixdate = WorksheetFunction.Match(datelookup, Range("k3:k4260"), 0)


comlookup = Range("c1").Offset(off, 0).Value
matrixcom = WorksheetFunction.Match(comlookup, Range("l2:dn2"), 0)


Range("k2").Offset(matrixdate, matrixcom).Value = Range("g1").Offset(off, 0).Value


off = off + 1
Loop


End Sub


any help would be appreciated!!

 

[Joe4]

One issue I see is with your variable declarations:

[COLOR=#333333]Dim off As Integer[/COLOR]
[COLOR=#333333]...[/COLOR]

[COLOR=#333333]off = 1[/COLOR]

[COLOR=#333333]Do While off < 4219912[/COLOR]

The maximum Integer value you can have is 32767.
You need to use "Long" instead.
See: http://www.informit.com/articles/article.aspx?p=339929&seqNum=2