counting "="

[rockhammer]

Is there a way to make something like =countif($a$1:$a$100,"=") work as
intended?

The function is returning zero when there are clearly cells with the text
"=" in it. If i were to use something like $a:$a as the range, it will just
count all the blank cells in column A.

Thanks.

 

[Bernard Liengme]

Try =COUNTIF(A:A,"*=*")
best wishes

 

[Keithlo]

I think it's because the = sign is not text but part of the formula.
Although I also can't get it to work when I make it text. However, this code
does work:

Dim Counter, c
Counter = 0
For Each c In Selection

If c.Formula Like "*=*" Then
Counter = Counter + 1
End If

Next c

MsgBox (Counter)

So VBA can handle it at least. My code assumes you select your range and
want output to a message box, but you could change it to be a pre-determined
range and put the result in a cell. I don't understand why the Excel
function doesn't work on an = sign as a string, but I am not surprised it
doesn't work when its part of a formula because then it's not part of the
value of the cell. And I know that Excel functions don't seem to have a
contains or like feature, which VBA does have.

Hope this helps.

Keith

 

[Keithlo]

It just occured to me that you may want to distinguish between cells with =
in the formula and cells with = in the value. If you only want cells with =
in the value you could change the code in my previous post from

c.Formula Like "*=*" to
c.Value Like "*=*"

Keith

 

[rockhammer]

Thanks, Keith & Bernard.

*=* is perfect since I'd like to avoid macros and I've verified that it works.