# Can't find reason for overflow

#### mbaars

##### New Member
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:

Code:
``````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:

Code:
``````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.

Milan

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
You need

Rich (BB code):
``Dim i As Long``

You need:

Dim i As Long

The maximum value of an Integer is 32,767.

Wauw,

Thanks for the quick reply, indeed it works!

I'd use:

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

Replies
8
Views
106
Replies
1
Views
233
Replies
3
Views
215
Replies
7
Views
630
Replies
1
Views
163

1,203,502
Messages
6,055,774
Members
444,822
Latest member
Hombre

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back