Can't find reason for overflow

[mbaars]

Hi,

The situation is as follows, I have a large expediting list with different items in it. The list consist of 55000 item each with it's unique I.D. in storage. On of the many manufacturers has an excel list in which the ID's of the manufacturer and our own ID's are coupled and I want to import the ID's in to my expediting list. So I use a vlookup to find the corresponding ID's in the other excel sheets. However I want to add them to the existing list i have so i use the following Macro:

Sub Macro1100()
Dim mySheet As Worksheet, myBook As Workbook
Set myBook = Excel.ActiveWorkbook
Set mySheet = myBook.Sheets("Expediting list")
Dim i As Integer
 
For i = 2 To 10000
   If mySheet.Cells(i, 24).Value <> "" Then
        mySheet.Cells(i, 25).Value = mySheet.Cells(i, 24).Value
   End If
Next i

End Sub

Because of the overflow problem i've cut the macro's in to pieces. It all goes fine fine fine, until I reach the 30000. Here it starts to give overflow problem's out of the blue, indepenten of how small I make te range, for example:

Sub Macro325350()
Dim mySheet As Worksheet, myOtherSheet As Worksheet, myBook As Workbook
Set myBook = Excel.ActiveWorkbook
Set mySheet = myBook.Sheets("Expediting list")
Dim i As Integer
 
For i = 32501 To 33000
   If mySheet.Cells(i, 24).Value <> "" Then
        mySheet.Cells(i, 25).Value = mySheet.Cells(i, 24).Value
      j = j + 1
   End If
Next i

End Sub

Two question, I have the feeling this is an extremely difficult way of doing it. Secondlym, suppose I want to keep on doing it this way and for the sake of understanding Excel better; why does it suddenly give an overflow error while it does not do that for the previous lines.

Thanks in advance,

Milan

 

[VoG]

You need

Dim i As Long

 

[Andrew Poulsom]

You need:

Dim i As Long

The maximum value of an Integer is 32,767.

 

[mbaars]

Wauw,

Thanks for the quick reply, indeed it works!

 

[snb_]

I'd use:

Sub M_snb()
   with activeworkbook.Sheets("Expediting list").cells(1).currentregion.columns(25)
      .value=.offset(,-1).value
    end with
 End Sub