Applying a 3D formula to all worksheets rather than specific worksheets

[OleMiss2010]

I am creating a workbook that will hold many worksheets and have a summary page at the front. I found and slightly augmented a UDF to count some components from the worksheets and put them in the summary tab. However, the UDF requires that I list out all of the worksheets that it is searching (see below).


Public Function CntIf3D(rng As Range, V As Variant, ParamArray arglist() As Variant)

Application.Volatile


CntIf3D = 0

For Each arg In arglist

CntIf3D = WorksheetFunction.CountIf(Sheets(arg).Range(rng.Address), V) + CntIf3D

Next


End Function


I have a macro that will put the function in a desired cell. The macro currently says:

"=CntIf3D(R3C2,""<>"",""Worksheet"",""Worksheet1"",""Worksheet2"")"


How can I essentially replace ""Worksheet"",""Worksheet1"",etc. with something like "Every Worksheet in Sheets" and make the function work?

 

[Luke M]

You "could" change the UDF to be:

[pre]

Public Function CntIf3D(rng As Range, V As Variant)
Dim ws As Worksheet
Application.Volatile
CntIf3D = 0
For Each ws In ThisWorkbook.Worksheets
CntIf3D = WorksheetFunction.CountIf(ws.Range(rng.Address), V) + CntIf3D
Next
End Function
This forces the function to look at all worksheets. Another idea would be to pass the numerical arguments indicating the starting/ending position of sheets to look at (say, sheets 2 through sheets 10)

Then you could do something like

Public Function CntIf3D(rng As Range, V As Variant, StartNum As Integer, EndNum As Integer)
Dim ws As Worksheet
Application.Volatile
CntIf3D = 0

For i = StartNum To EndNum
CntIf3D = WorksheetFunction.CountIf(Sheets(i).Range(rng.Address), V) + CntIf3D
Next

End Function

[/pre]

 

[OleMiss2010]

I like the second suggestion best for what I am trying to do, but two questions:

1) What should be entered into the cell? I thought it would be =cntif3d($B$3,"<>",2,4) where I wanted to count cell B3 in worksheets 2-4 that were not blank. That returned a #Name? error.


2) Is there any way I could remove the EndNum portion and only have a StartNum? Since I have a summary tab I want it to count all of the non-summary tab cells. I could of course subtract the summary tab number from the entire workbook as a way around that I guess.

 

[OleMiss2010]

I figured out the first issue, but the second question remains.

 

[Luke M]

1) That looks right, and your formula is working just fine on my end...is there an error possibly in one of the B3 cells?

Are there at least 4 worksheets? Did the code get put into a module? Usually the NAME error occurs when XL can't find the function.

Of note, I made a small error in code. It should state "Worksheets(i)" not "Sheets(i)". Don't want to be looking at any charts...


2) Excluding a single worksheet would be fairly simple. Assuming your summary sheet has a nice name like "summary", modifying the first UDF becomes:

[pre]

Public Function CntIf3D(rng As Range, V As Variant)
Dim ws As Worksheet
Application.Volatile
CntIf3D = 0
For Each ws In ThisWorkbook.Worksheets
'Which worksheet do you want to skip?
If ws.Name <> "Summary" Then
CntIf3D = WorksheetFunction.CountIf(ws.Range(rng.Address), V) + CntIf3D
End If
Next
End Function

[/pre]
Also, if you're just looking at a single cell and/or checking for non blanks, you could use the 3d formula:

=COUNTA(Sheet2:Sheet4!$B$3)

 

[OleMiss2010]

Works perfectly. Thanks a ton Luke M.