# Formula/String Length - out of memory msg

#### JPBJR

##### New Member
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### pbornemeier

##### Well-known Member
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.``

Replies
1
Views
114
Replies
3
Views
324
Replies
4
Views
244
Replies
1
Views
592
Replies
1
Views
270

1,190,841
Messages
5,983,187
Members
439,827
Latest member
JohnExcelHelp99

### 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.

### Which adblocker are you using?

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

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