Crashing Macro, Not Enough Memory

[newsuser]

Hello,

I'm writing a macro that processes and deletes data retrieved from
internet through a webquery in order to get uniformely formatted data.
The macro loops consistently 15 times correctly and then crashes on the
statement:

Worksheets(asset).Range("A:A").FormulaR1C1 = "=left(rc[9],3)"

At first I thought this was really a memory problem as the filesize
exploded by running the macro. The above statement creates sensible
data in the relevant lines, but in the lines below the relevant data
(about line 200 - 65536) it displays an error.

I attempted to get this solved by adding a statement to delete the
obsolete data. This works perfect in keeping the filesize checked. But
the macro keeps crashing at exactly the same location and in exactly
the same way.

Does anyone know what causes this problem and how to solve it?

Many Thanks
Wilco

 

[Dave Peterson]

Instead of populating the complete column, can you limit the range.

For instance, if I could use column B to determine the last row that should get
the formula:

dim LastRow as long
with worksheets(asset)
lastrow = .cells(.rows.count,"B").end(xlup).row
.range("a1:a" & lastrow).formular1c1 = "=left(rc[9],3)"
end with

Maybe just using column J would be sufficient???

Hello,

I'm writing a macro that processes and deletes data retrieved from
internet through a webquery in order to get uniformely formatted data.
The macro loops consistently 15 times correctly and then crashes on the
statement:

Worksheets(asset).Range("A:A").FormulaR1C1 = "=left(rc[9],3)"

At first I thought this was really a memory problem as the filesize
exploded by running the macro. The above statement creates sensible
data in the relevant lines, but in the lines below the relevant data
(about line 200 - 65536) it displays an error.

I attempted to get this solved by adding a statement to delete the
obsolete data. This works perfect in keeping the filesize checked. But
the macro keeps crashing at exactly the same location and in exactly
the same way.

Does anyone know what causes this problem and how to solve it?

Many Thanks
Wilco

 

[newsuser]

Excellent. It took me a bit to figure out how it works (pretty much a
novice) but it solves the error and seems to be a lot faster than my
original solution too. Thanks a lot!

Wilco