Does object.Offset have an upper limitation in Range Row increases?

[dbridges]

I have a VBA module I have written for a .DAT file that I am running through Excel.
The code is working as expected until I run into this section:

...

i = 1
Set sortCell = r1.Cells(i, 20)
Set r1 = ActiveSheet.Range(Cells(1, 1), Cells(A, 20))
Do While i < A
    sortCell.Value = r1.Cells(1, 1).Value & "|" & r1.Cells(1, 5).Value & "|" & r1.Cells(1, 6).Value & "|" & r1.Cells(1, 7).Value & "|" & r1.Cells(1, 8).Value & "|" & r1.Cells(1, 9).Value & "|" & r1.Cells(1, 10).Value & "|" & r1.Cells(1, 11).Value & "|" & r1.Cells(1, 12).Value & "|" & r1.Cells(1, 13).Value & "|" & r1.Cells(1, 14).Value & "|" & r1.Cells(1, 15).Value & "|" & r1.Cells(1, 16).Value & "|" & r1.Cells(1, 17).Value & "|" & r1.Cells(1, 18).Value
    Set sortCell = sortCell.Offset(1, 0)
    Set r1 = r1.Offset(1, 0)   
    i = i + 1
Loop

...
There are 37011 rows in the sheet this month. 37011 is the value of A and is calculated in a previous section.
When I get to Set r1 = r1.Offset(1, 0) I get a 1004 Application-defined or object-defined error. This occurs in row 28527 of the spreadsheet.
The application allows sortCell row to get set to 28527 but when it tries to increase the range row from 28526 to 28527, I get the error message.
I use watch to track the values.
Has anyone run into this and have any advice?
thanks

[Br1an_g]

you need to add the

.Activate

to the offset code to make it work.

if you are incrementing a row number it would be better to use a counter to do this, or do CurrentRowNumber +1

[dbridges]

Thanks for the advice but that did not solve it. If I add .activate at the end, I get an object must be declared error message.
Plus, it had recognized and written to 28526 cells previously so the cells were already active via the ActiveSheet function to declare those cells.

[Br1an_g]

Ok,

why use the offset at all?
if you put your set into the loop at the start, and your 'i' count is incrementing so doing your offset for you.

do the same for the r1 range and increment the rows using a counter.

why i suggested it previously as you were setting a range, so the .activate wouldnt have helped (unless you are cycling through cells real time)

i = 1
Do While i < A
    Set r1 = ActiveSheet.Range(Cells(1, 1), Cells(A, 20))'increment this using a counter similar to 'i'
    Set sortCell = r1.Cells(i, 20)
    sortCell.Value = r1.Cells(1, 1).Value & "|" & r1.Cells(1, 5).Value & "|" & r1.Cells(1, 6).Value & "|" & r1.Cells(1, 7).Value & "|" & r1.Cells(1, 8).Value & "|" & r1.Cells(1, 9).Value & "|" & r1.Cells(1, 10).Value & "|" & r1.Cells(1, 11).Value & "|" & r1.Cells(1, 12).Value & "|" & r1.Cells(1, 13).Value & "|" & r1.Cells(1, 14).Value & "|" & r1.Cells(1, 15).Value & "|" & r1.Cells(1, 16).Value & "|" & r1.Cells(1, 17).Value & "|" & r1.Cells(1, 18).Value
    i = i + 1
Loop

trying it like this will rule out an issue with the offset command.

[dbridges]

Brian,

Thanks for the kick. After you mention about why use the Offset, I went back and looked at what I was trying to accomplish. I had too many unnecessary variables. The final code, which allows me to create a very nifty looking Subtotal spreadsheet, is:

j = 1
    For j = 1 To A
    ActiveSheet.Cells(j, 2).Value = ActiveSheet.Cells(j, 1).Value & "|" & ActiveSheet.Cells(j, 6).Value & "|" & ActiveSheet.Cells(j, 7).Value & "|" & ActiveSheet.Cells(j, 8).Value & "|" & ActiveSheet.Cells(j, 9).Value & "|" & ActiveSheet.Cells(j, 10).Value & "|" & ActiveSheet.Cells(j, 11).Value & "|" & ActiveSheet.Cells(j, 12).Value & "|" & ActiveSheet.Cells(j, 13).Value & "|" & ActiveSheet.Cells(j, 14).Value & "|" & ActiveSheet.Cells(j, 15).Value & "|" & ActiveSheet.Cells(j, 16).Value & "|" & ActiveSheet.Cells(j, 17).Value & "|" & ActiveSheet.Cells(j, 18).Value & "|" & ActiveSheet.Cells(j, 19).Value
    ActiveSheet.Cells(j, 5).Value = 1
    Next j

Thanks for your help.

[Br1an_g]

thats what i was aiming for...using a variable count within your range.