What is the item "How Many"?
 

When you need to know howmany, you build the range as bob shows in the sub
Howmany3, and in the macro that needs to know howmany, you call the howmany
function, passing it the range where you want to know howmany. You don't
store the value anywhere - you have it calculated each time you need it.

Regards,
Tom Ogilvy

"Bruce Roberson" wrote in message
...
In this procedure that Bob Phillips helped me come up
with, I think it does its work correctly in that it comes
up with the number of lines in column 1 of a current
region where the value is a 3.

But once the end sub is encountered, isn't this value lost
unless it is saved to a cell somewhere?

The reason I come to that conclusion is when I go through
in debug mode and I put a watch on the variable howmany,
it got the answer, but when the macro is run out, it says
<out of context for the value, and it also
says "variant/Empty" for the type.

So what do I need to do? Do I need to put a statement
before End Sub that stores that value somewhere in a range?
If I can get to this value in another macro during the
same spreadsheet session, then could someone tell me how
to do that part?

Thanks,


Bruce
__________________________________________________ _________

Sub Howmany3()
Dim cRows As Long
cRows = Cells(Rows.Count, Range("Start").Column).End
(xlUp).Row
myNum = Howmany(Range(Cells(1, Range("Start").Column),
Cells(cRows, _
Range("Start").Column)), 3)
End Sub
Function Howmany(rng As Range, val)
Dim cell As Range
Dim cMatch As Long
For Each cell In rng
If cell.Value = val Then
cMatch = cMatch + 1
End If
Next
Howmany = cMatch
End Function