Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning left part of cell before a character | Excel Discussion (Misc queries) | |||
#N/A if cell contents not part of an array | Excel Discussion (Misc queries) | |||
Array formula returning the cumulative sum | Excel Worksheet Functions | |||
Array formula returning terminated employees | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |