Macro Loop Throws 1004 error

[John Bailo]

I wrote a loop to skip through some data (with the idea of selecting and
copying it). For some reason, when i=44 and the ActiveCell = HM2, a
1004 error is thrown on the .Offset(0,i).Select method.

Dim i
i = 0

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.Offset(0, i).Select
i = i + 4

Loop

There is no difference in the data on worksheet in the columns before
and after the HM column. It is not at the edge of the worksheet.


This is the error:

"Run-time error '1004'
Application-defined or object-defined error"

What could cause this?

 

[John Bailo]

I simplified it and this works:

Do While IsEmpty(ActiveCell.Offset(0, 4)) = False

ActiveCell.Offset(0, 4).Select

Loop


I forgot that the Offset sets a new ActiveCell, so I don't need a
counter (i).

 

[cathellisuk]

John,
I don't know why you got that error message but if you want to select
from the active cell to furthest cell on the right that isn't empty
then

Range(Selection, Selection.End(xlToRight)).Select
may be another way of doing it.

Cath

 

[Pete_UK]

If your ActiveCell was HM2 and you tried to offset it by 44 columns,
this would put you off the sheet, so this is why you got the run-time
error.

Hope this helps.

Pete

 

[Tom Ogilvy]

Dim i
i = 4
Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.Offset(0, i).Select

Loop

would also work.

here is the reason for your error:

? Range("HM2").column
221
? 221 + 44
265

265 is beyond the last column which is 256. So you just had a logic error
in your code.