Upper and Lower case in a Count

I have macro that counts an "x" in a particular cell on multiple sheets and then reports it back on one sheet. It is all input manually by several users, so sometimes they use Upper case (X) and sometimes they use Lower case (x). How do I make Excel ignore the case and count if it is upper and/or lower case?

Originally Posted by CanMan12

I have macro that counts an "x" in a particular cell on multiple sheets and then reports it back on one sheet. It is all input manually by several users, so sometimes they use Upper case (X) and sometimes they use Lower case (x). How do I make Excel ignore the case and count if it is upper and/or lower case?

I think we need the macro to figure out the best choice.

Can you copy it here?

Thanks.

Function GetCnt(st As String)
Application.Volatile
Dim cnt As Long
cnt = 0
For i = 2 To Sheets.Count
If Sheets(i).Range(Application.Caller.Address) = st Then cnt = cnt + 1
Next i
GetCnt = cnt
End Function

Originally Posted by CanMan12

Function GetCnt(st As String)
Application.Volatile
Dim cnt As Long
cnt = 0
For i = 2 To Sheets.Count
If Sheets(i).Range(Application.Caller.Address) = st Then cnt = cnt + 1
Next i
GetCnt = cnt
End Function

Ok,

change that function into:

Function GetCnt(st As String)
Application.Volatile
Dim cnt As Long
cnt = 0
For i = 2 To Sheets.Count
    If Sheets(i).Range(Application.Caller.Address) = Ucase(st) Or Sheets(i).Range(Application.Caller.Address) = Lcase(st) Then cnt = cnt + 1
Next i
GetCnt = cnt
End Function

and let me know if it worked.


ciao

Worked like a charm! Thank you very much!

Originally Posted by CanMan12

Worked like a charm! Thank you very much!

You are welcome.

ciao