Formula/String Length - out of memory msg

JPBJR

New Member
Joined
May 8, 2002
Messages
32
I have a declared a variable 'x' as a string which I initialize with "=" (an equal sign).
Into this string I concatenate " +'worksheetname'!column&row " over about 130 worksheet names.

Essentially The macro looks through 130 worksheets finding the appropriate cell in each and creates a string for that worksheet/cell that I concatenate onto 'x.' I then write 'x' onto its own worksheet.
x is then the formula that sums/adds each of the correct cells in the 130 worksheets.

This all works GREAT while the string length of x is < 130 characters. I get error message '7' -Out of Memory error when len(x)=131 or greater -- or thereabouts.

Should I declare/dimension 'x' as some type other than 'string?' I assume this an excel limiatation, not a limitation of my PC.

I am using Excel 2003.

Thanks

John
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I just created a string variable in excess of 100000 characters. I am not sure where the out of memory error is coming from, but is is not due to a string variable of only 131 characters. Can you split up large formula into 10 cells, each concatenating 13 worksheet cells then a 14th cell that concatenating those 13 cells.

Are the cells you are pulling from the 130 worksheets hard coded? Or are they dependent on other things? in either case, if you could get the value of the cell that you are interested in on each worksheet into the same cell on each worksheet (say AZ143, then you could use a formula similar to
Code:
=SUM(Sheet1:Sheet130!AZ143) to add them up.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top