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 the
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) the
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|
+-------------------------------------------------------------------+

--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33071

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


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33071

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

regards
FSt1

"colwyn" wrote:


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 the
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) the
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|
+-------------------------------------------------------------------+

--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33071

Yes, the following appears:


Run-time error '1004':
Cut method of Range class failed


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 wondering
if it's a memory thing?? (I've got 2Gb ram).
Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33071

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" wrote:


Yes, the following appears:


Run-time error '1004':
Cut method of Range class failed


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 wondering
if it's a memory thing?? (I've got 2Gb ram).
Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33071

Thanks FSt1. What I'm trying to do is cut and paste from individual
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 to
achieve.
Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33071

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
if you want.
post here when you send so i will know to check.
Regards
FSt1


"colwyn" wrote:


Thanks FSt1. What I'm trying to do is cut and paste from individual
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 to
achieve.
Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33071