change code to work over multiple sheets

  • Thread starter Thread starter cda_cmd
  • Start date Start date
C

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?
 
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
 
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
 
Back
Top