change code to work over multiple sheets

[cda_cmd]

I have a function:

Code:
--------------------
Public Function PositionCount(pos As String, rge As Range) As Integer

Dim counter As Integer

counter = 0

For Each r In rge.Cells

If r.Value = pos Then counter = counter + 1

Next r

PositionCount = counter


End Function
--------------------

The function is used like this:
=PositionCount(A4,'1'!L13:L300)
Cell A4 simply contains a string. This string is what is being
'searched' for.

I have 31 sheets, named 1 .. 31

At the moment I have an annoyingly long formula like this:
=PositionCount(A4,'31'!L13:L300)+
...
PositionCount(A4,'1'!L13:L300)
Each of those checks the same range on a different sheet.

How should my code be changed so that it will do exactly the same
thing, but look up the given range on the 31 different sheets?

 

[Eric_MUC]

Hi,

what about if you change the code this way:

Code:
--------------------
Public Function PositionCount(pos As String, rge As Range) As Integer

Dim counter As Integer

counter = 0

For Each shtSheet in Activeworkbook.Sheets

For Each r In rge.Cells

If r.Value = pos Then counter = counter + 1

Next r

Next shtSheet

PositionCount = counter

End Function
--------------------

Make sure you change your formula from the value '31'!L13:L300 to L13:L300
for rge.

Did this help you?

Best wishes,
Eric

 

[Peter T]

Function CountifAcross(vCriteria, sFirst As String, _
sLast As String, vRng) As Long
Dim ws As Worksheet
Dim sAddr As String
Dim sCtrit As String
Dim nCnt As Long

Application.Volatile ' seems to require this

If TypeOf vRng Is Range Then
sAddr = vRng.Address
Else
sAddr = vRng
End If

scrit = "=" & CStr(vCriteria)
For Each ws In Worksheets
If ws.Name = sFirst Then b = True
If b Then
nCnt = nCnt + Application.CountIf(ws.Range(sAddr), scrit)
End If
If ws.Name = sLast Then Exit For
Next

CountifAcross = nCnt
End Function

Be careful if moving sheets.

If you search "Countif Across *Sheets" over in excel.misc or
..worksheetfunctions there are plenty of formulas that would avoid needing a
UDF (but require populating a list of sheet names)

Regards,
Peter T