Various error 1004's associated with copying ranges. Excel Gurus please help!!
 

I have a macro that takes a table of data, typically 8-10 columns and
approx 40,000-60,000 rows, and processes it by adding new columns to
the right of it.

Because of the intensive calculations involved, I do this by copying
formulas one row at a time, calculating, and converting the result to
value before moving on to the next row.

The problem I am having is that for some reason my formulas work well
until they get to row 10925, at which point I get the following error:

Run time error 1004 Application defined or Object Defined error
The statements that generated the error:
' convert formula to value
With ActiveSheet.Range(Cells(lefttop, colfound), Cells(rightbottom,
cc))
.Formula = .Value
End With

I tried these variations as well but got the same error, always at the
same row
ActiveSheet.Range(Cells(lefttop, colfound), Cells(rightbottom,
cc)).value = ActiveSheet.Range(Cells(lefttop, colfound),
Cells(rightbottom, cc)).value
ActiveSheet.Range(Cells(lefttop, colfound), Cells(rightbottom,
cc)).formula = ActiveSheet.Range(Cells(lefttop, colfound),
Cells(rightbottom, cc)).value

Note: Thinking that perhaps there might have been something problematic
about the data in that row, I deleted it but found that it made no
difference.


After spending many hours on these forums unsuccessfully looking for a
solution, I finally removed the statement altogether and re-ran the
macro. This time I got the following error prompted by a statement that
was working just fine previously:

Run time error 1004 Clear method of range class failed
The statement associated with the error:
Worksheets(wrksheet).Range(Cells(lefttop, colfound), Cells(usedrows,
cc)).Clear


And after removing THAT statement in turn, I suddenly get another error
message for a statement that hitherto was working just fine:

Run time error 1004: copy method of range class failed
The statement associated with the error:
ActiveSheet.Range(Cells(formularow, colfound), Cells(formularow,
cc)).Copy ActiveSheet.Range(Cells(lefttop, colfound),
Cells(rightbottom, cc))


I would appreciate any help on this. It has been the source of much
frustration for me and I desperately need this issue fixed to get my
work done. One thing I might mention, also, is that I typically DO NOT
define variables in my code but let excel do that for me.


Thanks!

Various error 1004's associated with copying ranges. Excel Gurus please help!!
 

What is the value of each of the "Cells" variables when the error occurs?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



wrote in message
I have a macro that takes a table of data, typically 8-10 columns and
approx 40,000-60,000 rows, and processes it by adding new columns to
the right of it.

Because of the intensive calculations involved, I do this by copying
formulas one row at a time, calculating, and converting the result to
value before moving on to the next row.

The problem I am having is that for some reason my formulas work well
until they get to row 10925, at which point I get the following error:

Run time error 1004 Application defined or Object Defined error
The statements that generated the error:
' convert formula to value
With ActiveSheet.Range(Cells(lefttop, colfound), Cells(rightbottom,
cc))
.Formula = .Value
End With

I tried these variations as well but got the same error, always at the
same row
ActiveSheet.Range(Cells(lefttop, colfound), Cells(rightbottom,
cc)).value = ActiveSheet.Range(Cells(lefttop, colfound),
Cells(rightbottom, cc)).value
ActiveSheet.Range(Cells(lefttop, colfound), Cells(rightbottom,
cc)).formula = ActiveSheet.Range(Cells(lefttop, colfound),
Cells(rightbottom, cc)).value

Note: Thinking that perhaps there might have been something problematic
about the data in that row, I deleted it but found that it made no
difference.


After spending many hours on these forums unsuccessfully looking for a
solution, I finally removed the statement altogether and re-ran the
macro. This time I got the following error prompted by a statement that
was working just fine previously:

Run time error 1004 Clear method of range class failed
The statement associated with the error:
Worksheets(wrksheet).Range(Cells(lefttop, colfound), Cells(usedrows,
cc)).Clear


And after removing THAT statement in turn, I suddenly get another error
message for a statement that hitherto was working just fine:

Run time error 1004: copy method of range class failed
The statement associated with the error:
ActiveSheet.Range(Cells(formularow, colfound), Cells(formularow,
cc)).Copy ActiveSheet.Range(Cells(lefttop, colfound),
Cells(rightbottom, cc))


I would appreciate any help on this. It has been the source of much
frustration for me and I desperately need this issue fixed to get my
work done. One thing I might mention, also, is that I typically DO NOT
define variables in my code but let excel do that for me.


Thanks!