Returning an Array as part of creating a cell formula

G

Guy Hoffman

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top