Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting "Per Item" Pivot Chart Results when data rows are identica | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |