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