Run-Time error "7" out of memory

[spilner]

hi,ive thousands of sheets.

im trying to run this vba but it end with error.i saved the file as macro enabled or theres any other way to save macro file?

Public Sub BuildMyFormula()
Dim sFormula As String
'Provide the cell address in format like A1, B1 etc
CellName = InputBox("Please provide cell address like A1", "Cell Address")
sFormula = ""
'This builds up the formula on its own
For i = 1 To Sheets.Count
sFormula = sFormula & "(" & Sheets(i).Name & "!" & CellName & ">=50)" & "*" & _
Sheets(i).Name & "!" & CellName & ","
Next i
'Removes unnecessary comma
sFormula = Left(sFormula, Len(sFormula) - 1)
'Sets the formula in active cell
ActiveCell.Formula = "=SUM(" & sFormula & ")"
End Sub

can someone help how to fix?

 

[spilner]

when i click debug,it highlighted yellow this line

ActiveCell.Formula = "=SUM(" & sFormula & ")"

 

[spilner]

hope someone can fix it.thanks a lot

 

[Ruddles]

You've got thousands of sheets in the same workbook - is that what you're saying?

What is the length of the string sFormula at the point where you assign it to the active cell?

As an aside, do any of your sheet names have spaces in them?

 

[spilner]

yes in the same workbook.
i just put A1 in the box when i run the code.
my sheet names only numbers.

check this post
http://www.mrexcel.com/forum/showthread.php?t=559985

 

[Ruddles]

What is the length of the string sFormula at the point where you assign it to the active cell?

If there's a reference to each sheet in that formula and there are over a thousand sheets, I imagine the formula us just too long for Excel to cope with.

I can't imagine why you should need that many sheets in a properly designed workbook/database. Perhaps you should be looking at that before you go very much further.

 

[spilner]

What is the length of the string sFormula at the point where you assign it to the active cell?
no formula,i put the number manually.

i want to sum all sheets in the same cell A1 with a condition.must sum all more than 50 only.dont count if less than 49.

thanks man

 

[Ruddles]

no formula,i put the number manually.

Yes, there definitely is a formula - it's being assigned to the active cell by this piece of code:-

ActiveCell.Formula = "=SUM(" & sFormula & ")"

What I am asking is: what is the length of the string sFormula at that point? In other words, what is the length of the formula which your code is trying to cram into the cell?

When you get the error message and an option to End or Debug, select Debug, then in VBA press Ctrl-G to view the Immediate window and type ?len(sformula) followed by Enter.

What value do you get?

 

[Norie]

If I run that code for 1000 pseudo-sheets (ie replacing the sheet name with i) the length of sformula is 19910 characters.

This is what Ruddles is trying to say I think, the formula is way to long to put in a cell.

By the way, you really need to pay attention to Aladin in that thread you link to.

Try this which creates a list of the worksheets and names it SheetList.

Then it inputs the criteria in B1 and the Aladin's formula in C1.

Public Sub BuildMyFormula()
Dim ws As Worksheet
Dim wsSummary As Worksheet
Dim rng As Range

    Set wsSummary = Worksheets("Summary")

    Set rng = wsSummary.Range("A2")

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsSummary.Name Then
            rng.Value = ws.Name
            Set rng = rng.Offset(1)
        End If
    Next ws
 
    wsSummary.Range("A2", rng.Offset(-1)).Name = "SheetList"

    wsSummary.Range("B1") = 50    ' criteria for sum
 
    wsSummary.Range("C1").Formula = "=SUMPRODUCT(SUMIF(INDIRECT(""'""&SheetList&""'!A11""),"">""&B1))"
 
End Sub

With this the formula is only ever about 50-60 odd characters, depending on the cell you want to sum and the cell with the criteria.

You can use CellName in the formula like this.

    wsSummary.Range("C1").Formula = "=SUMPRODUCT(SUMIF(INDIRECT(""'""&SheetList&""'!" & CellName & """),"">""&B1))"