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
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.
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.
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).
