code stops working after X rows.

[colwyn]

I'm using Excel 2007 and my s/s is 360000 rows deep.
To cut+paste formulas+formats from one column to another I'm using th
following code:


Code
-------------------

Sub move_formula_and_formats_from_I_to_L()

Dim cell As Range

Application.ScreenUpdating = False


For Each cell In Range("I1", Cells(Rows.Count, "I").End(xlUp))
With cell
If .HasFormula And Not .Offset(1).HasFormula Then
.Cut Destination:=.Offset(1, 3)
End If
End With
Next cell

Application.ScreenUpdating = True

End Sub


-------------------


The code stops working after 159000 rows and highlights (in yellow) th
line:
.Cut Destination:=.Offset(1, 3)

Can anyone help with this??

Small XML attachment may help better understand.
Big thanks.
Colwyn

+-------------------------------------------------------------------
|Filename: example.xls
|Download: http://www.thecodecage.com/attachment.php?attachmentid=18
+-------------------------------------------------------------------

 

[The Code Cage Team]

Perhaps replace
Range("I1", Cells(Rows.Count, "I").End(xlUp))
with
Range("I1:I" & Range("I" & rows.Count).End(xlUp).row

--
The Code Cage Tea

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com

 

[FSt1]

hi
do you get any error messages? does the code do as expected up to 159000 rows?

regards
FSt1

 

[colwyn]

Yes, the following appears

Run-time error '1004'
Cut method of Range class faile

When I click 'debug' the macro code appears with the line

.Cut Destination:=.Offset(1, 3

highlighted in yellow
Big thanks
Colwyn
*-Post posted before response, posts merged!*
No. unfortunately the code stops at exactly the same row. I'm wonderin
if it's a memory thing?? (I've got 2Gb ram).
Big thanks.
Colwyn

 

[FSt1]

hi
error 1004 is a catchall error. since the cut command was use, it just said
cut command failed and didn't say why. humm..
my suspicions.....
a long time ago, i learned that it was unwize to use the copy(and
worst...cut) command in a loop. one or two time in a macro is ok but in a
loop, it will eventually crash the macro with memory problems. i don't know
why....something to do with the clipboard. i had the same problem with
lotus(which is where i discovered the problem and later discovered that excel
had the same problem.)
the solution is to use variables and have one variable (sorce) equal
another(destination).

i fiddled with this yesterday but was having problems understanding your if
criteria logic. is the syntax correct? exactly what are you trying to do?

regards
FSt1

 

[colwyn]

Thanks FSt1. What I'm trying to do is cut and paste from individua
cells in column I and paste them to cells one row down in column L.
See attachment in my first post above. Column P shows what I want t
achieve.
Big thanks.
Colwyn

 

[FSt1]

hi
i'm stil stuck on 2003. i tried to download your example but got a page not
found.
it's your selection criteria that blunted all my testing. this line....

If .HasFormula And Not .Offset(1).HasFormula then

notice the offset only has one number. is this right???
does your data have blanks in it????

email a 2003 compatible file to
(e-mail address removed) if you want.
post here when you send so i will know to check.
Regards
FSt1

 

[colwyn]

FSt1 - thanks again. I don't know why 'example.xls' isn't working fo
you. It downloads for me every time. I'll attach it again below.

Re Offset having only one number the line below has two.
Data does have blank cells.

Please see if the enclosed attachment will download.
Big thanks.
Colwyn

+-------------------------------------------------------------------
|Filename: example.xls
|Download: http://www.thecodecage.com/attachment.php?attachmentid=20
+-------------------------------------------------------------------

 

[FSt1]

hi
still wont download. keep getting page not found. try the email address i
posted.
confirm: you want to move all formulas and formats one row down and 3
columns right.

regards
FSt1

 

[colwyn]

FSt1 -thanks again. Sorry the file isn't coming through. I've now
managed to input the formats+formula by cutting the s/s into two parts
and running the macro seperately on both. Maybe not best option but it
got me where I wanted to be. I think the s/s was just too big!
Big thanks.
Colwyn.

 

[FSt1]

glad to help?
regards
FSt1