What is the maximum value for row and column offse

Question

I know a similar question has been answered here:

What is the maximum value for row and column Range offset in VBA/Excel?

However, that error seemed to be related to the number of rows in the worksheet in Excel 2003. Since I am running Excel 2010 (with 65,536 rows), I don't think my error is related to my worksheet size.

Here is the line of code that is giving me Run-time error '6' Overflow:

Range("OutputStart").Offset(1 + (iCounter1 - 1) * iDataPoints * 26 + iCounter2 * iDataPoints, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

iCounter1 is an integer and its value is 13 iDataPoints is an integer and its value is 103 icounter2 is an integer and its value is 7

The result of the row offset calculation is 32,858, which indicates an Integer data type issue. Is the row offset limited to Integer data types? If so, is there any way to change that limitation to Long (or another data type)?

Thanks for any help you can provide!

Will

Answer 1

Range.Offset method is not limited to integer.

Offset has two parameters both are of type Variant

• RowOffset Optional Variant
• ColumnOffset Optional Variant

Numeric data in Variant:

Generally, numeric Variant data is maintained in its original data type within the Variant. For example, if you assign an Integer to a Variant, subsequent operations treat the Variant as an Integer. However, if an arithmetic operation is performed on a Variant containing a Byte, an Integer, a Long, or a Single, and the result exceeds the normal range for the original data type, the result is promoted within the Variant to the next larger data type. A Byte is promoted to an Integer, an Integer is promoted to a Long, and a Long and a Single are promoted to a Double. An error occurs when Variant variables containing Currency, Decimal, and Double values exceed their respective ranges.

Answer 2

Integers in VB are 16 bit. Use an unsigned int; or more ideally, a long.

Range takes optional variants as arguments, and numeric data is preserved if that's what is fed into it.

To cast a number as a long, just wrap it in the CLng(yournumber), or if your number gets huge, cast as a double with CDbl(yournum).

Answer 3

Don't use Integer: they are 16 bit signed values; their range is -32768 to 32767. That is why you're getting the overflows.

Use a Long instead.