"countif" in multiple worksheets using VBA

[romelsms1]

Hi all,

I’m truing to create a macro for “counting” cells that contain a particular character in multiple worksheets ( “X” in my case)
All my sheets have the same pattern (except the first which is a analysis sheet) so I have the same tables in the same position (but different inputs)

I made ​​a macro that works but only if place the “cell” in the code (first example) :
Function numara_daca()
Application.Volatile
mylast = Worksheets.Count
For j = 2 To mylast
With Worksheets(j)
If UCase(.Range("C11")) = "X" Then
numara_daca = numara_daca + 1
End If
End With
Next j
End Function

so if i enter in a cell from my worksheet =numara_daca() only works for cell C11 and here is my problem: I want to apply macro across multiple cells without having to enter them each time in the code. I wish I could enter it in cell function like this =numara_daca(C11) or =numara_daca(D12) or something like that


I tried to create a code....but does not work...gives me: #VALUE!
Function numara_daca1(cell As Range)
Application.Volatile
mylast = Worksheets.Count
For j = 2 To mylast
With Worksheets(j)
If UCase(.Range(cell)) = "X" Then
numara_daca1 = numara_daca1 + 1
End If
End With
Next j
End Function

any idea???

[CharlesH]

romelsms1

If you posted the same question on another forum please let us know.

http://www.excelforum.com/excel-prog...using-vba.html

[romelsms1]

romelsms1

If you posted the same question on another forum please let us know.

http://www.excelforum.com/excel-prog...using-vba.html

I did not know that is the same forum. please delete one of them

[mikerickson]

That is a different forum.
Posting the same question on different forums is known as cross-posting.

This link explains how to cross post properly and why it is in everyone's interest to do so.

[mikerickson]

As to your question, this function might work.
A formula like =CountIfSheets(C11, "X") should work.

[VBA]Function CountIfSheets(aRange As Range, xCriteria As String) As Double
Dim rangeAddress As String
Dim i As Long
Application.Volatile
rangeAddress = aRange.Address

With ThisWorkbook
For i = 2 To .Worksheets.Count
CountIfSheets = CountIfSheets + Application.CountIf(.Worksheets(i).Range(rangeAddress), xCriteria)
Next i
End With
End Function[/VBA]

[mikerickson]

A more controlable version would be.
The formula for your situation would be
=CountIfSheets(C11, "x", True, 1)

[VBA]Function CountIfSheets(aRange As Range, xCriteria As String, ParamArray sheetIndexes() As Variant) As Double
'aRange - range to count
'xCriteria - a criteria string ala CountIf
'optional ExcludeList - logical, are the listed sheets to be included in the sum or excluded. default=false
'optional sheetIndexes - a list of sheet indices, they can either be sheet names or index numbers.
'=CountIfSheets(A1:A10,"x", "Sheet1", "Sheet2") counts the number of "x" in Sheet1!A1:A10 and Sheet2!A1:A10
'=CountIfSheets(A1:A10,"x", True, 1) looks at every sheet except the first.

Dim rangeAddress As String
Dim sheetIndices As Variant
Dim ExcludeList As Boolean
Dim Low As Long, High As Long
Dim i As Long
Application.Volatile
rangeAddress = aRange.Address
sheetIndices = sheetIndexes
If UBound(sheetIndices) = -1 Then
Rem all sheets
With ThisWorkbook
For i = 1 To .Worksheets.Count
CountIfSheets = CountIfSheets + Application.CountIf(.Worksheets(i).Range(rangeAddress), xCriteria)
Next i
End With
Else
Rem specified sheets
Low = 0: High = UBound(sheetIndices)

Rem remove from list
If TypeName(sheetIndices(0)) = "Boolean" Then
ExcludeList = sheetIndices(0)
Low = 1
End If

If ExcludeList Then
Rem all sheets
With ThisWorkbook
For i = 1 To .Worksheets.Count
CountIfSheets = CountIfSheets + Application.CountIf(.Worksheets(i).Range(rangeAddress), xCriteria)
Next i
End With
Rem remove indicated
For i = Low To High
With ThisWorkbook.Worksheets(sheetIndices(i))
CountIfSheets = CountIfSheets - Application.CountIf(.Range(rangeAddress), xCriteria)
End With
Next i
Else
CountIfSheets = 0
For i = Low To High
With ThisWorkbook.Worksheets(sheetIndices(i))
CountIfSheets = CountIfSheets + Application.CountIf(.Range(rangeAddress), xCriteria)
End With
Next i
End If
End If
End Function[/VBA]