Formula/String Length - out of memory msg

[JPBJR]

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

 

[pbornemeier]

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

=SUM(Sheet1:Sheet130!AZ143) to add them up.