Returning an Array as part of creating a cell formula
 

I have the following Code that writes a following formula to a cell"

Worksheets("Summary").Range("H18").Formula
"=SUMPRODUCT(SUMIF(INDIRECT(""'""&AllSheets&""'!"" &CELL(""address"",a1)),""0""))"

The above code includes the use of a named range called "AllSheets".
created this named range by listing all sheets in the workbook in
column, selecting them and naming the selection "AllSheets"

I would like to have the code do this or better yet modify, the formul
above to reference a function that returns an array of all sheets i
the workbook.

I am aware of the following code that creates such an array but
cannot get the two to work together:

Dim Arr() As String
Dim I as Integer
Redim Arr(Sheets.Count-1)
For I = 0 To Sheets.Count - 1
Arr(i) = Sheets(I+1).Name
Next I
AllTheSheets = Application.Worksheetfunction.Transpose(Arr)

Can someone help me?

G

--
Message posted from http://www.ExcelForum.com