S
samer.kurdi
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!
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!