Overflow error VBA same code

Question

I have a pretty simple question. I wrote this code to look for a preexisting date so that I can replace it. This code has worked perfectly, but I created a copy of the file to run the same code with a separate data set and now the code gets hung up on the fourth line giving me an overflow error. Any idea as to what the root of the problem is?

Dim i As Integer, ValueToFind As Integer, LRow As Integer
intValueToFind = Sheet8.Range("K6")
Sheet3.Activate
LRow = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LRow
If Cells(i, 9).Value = intValueToFind Then
MsgBox ("Found value on row " & i)

1. Change all Integer to Long, 2. Forget you ever heard the word integer. — user4039065
– user4039065, Commented Jun 22, 2018 at 18:09
@jeeped awesome thank you, that worked perfectly. Just so I can understand, do you have an idea why it would work for one file and not another? — TDJ
– TDJ, Commented Jun 22, 2018 at 18:20
Because one worksheet has more rows than the maximum for a signed short integer (32,767) but neither had more rows than the maximum of a signed long integer (2,147,483,647). google-fu — user4039065
– user4039065, Commented Jun 22, 2018 at 18:25

Mathieu Guindon · Accepted Answer · 2018-06-22 18:43:16Z

2

Looks like Range("I" & Rows.Count).End(xlUp).Row is returning a value that's larger than an Integer can fit.

Integer is a 16-bit signed integer data type, which means its maximum positive value is 32,767, 2^15-1.

A worksheet can have many, many, many times more rows than that.

Use a Long instead, a 32-bit signed integer data type, with a maximum positive value of 2,147,483,647, 2^31-1.

Don't worry about allocating 32 bits for every integer - on modern computers things are optimized for Int32 anyway; you hardly ever need to use a 16-bit integer type.

answered Jun 22, 2018 at 18:43

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Overflow error VBA same code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related