VBA using arrays in creating cell formula

  • Thread starter Thread starter Guy Hoffman
  • Start date Start date
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". I
created this named range by listing all sheets in the workbook in a
column, selecting them and naming the selection "AllSheets"

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

I am aware of the following code that creates such an array but I
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?

GH
 
Maybe you could you use what you already started--a nice workbook name.

Option Explicit
Sub testme()

Dim myArr As Variant
Dim I As Long
Dim j As Long

ReDim myArr(1 To Worksheets.Count - 1)
j = 0
For I = 1 To Worksheets.Count
If LCase(Worksheets(I).Name) = "summary" Then
'do nothing
Else
j = j + 1
myArr(j) = Worksheets(I).Name
End If
Next I

ActiveWorkbook.Names.Add Name:="allthesheets", _
RefersToR1C1:=Application.Transpose(myArr)

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

End Sub

ps. I changed your loop to avoid the summary worksheet. I guessed that you
didn't want that included.



And both of these lines worked:

ActiveWorkbook.Names.Add Name:="allthesheets", _
RefersToR1C1:=Application.Transpose(myArr)


And

ActiveWorkbook.Names.Add Name:="allthesheets", _
RefersToR1C1:=myArr

I didn't have to use application.transpose at all.

Remember if you add a worksheet, you'll have to rerun your macro.


One more thing. Since your code is creating the name, you may want to make it
more difficult for the user to "fix" it. One way is to make it invisible to the
user:

With ActiveWorkbook.Names.Add(Name:="allthesheets", _
RefersToR1C1:=Application.Transpose(myArr))
.Visible = False
End With

Invisible names may stop most users.

And if you're going to work with names, do yourself a big favor and get a copy
of this:

Jan Karel Pieterse's (with Charles Williams and Matthew Henson) utility
"Name Manager.xla" from http://www.bmsltd.co.uk/mvp/
(or http://www.bmsltd.ie/mvp/)
 
Back
Top