Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or a Sub?
I'll try to describe what this is about, but in QPW this
part was just a subroutine. This is the first part of a bunch of postings I'll need to do relating to creating some CSV files. The purpose of this routine or function depending on the suggestion is to scan a column Sub CreateTaxCSVFile() Range("Start").Offset(1, 0).Select 'create starting point' Selection.CurrentRegion.Select 'to select a block of data' Function or call to Howmanytype3() ---------------------------------- With the current region selected, I need to have the function or sub to determine how many occurences of the numeric value 3 occurs in the first column of this selected region. The function needs to store that number of occurences as a value for use later on in the macro. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or a Sub?
Bruce,
This is a simple function to do it 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 Call it like so myNum = HowMany(Range("Start").Offset(1, 0).CurrentRegion,3) -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Bruce Roberson" wrote in message ... I'll try to describe what this is about, but in QPW this part was just a subroutine. This is the first part of a bunch of postings I'll need to do relating to creating some CSV files. The purpose of this routine or function depending on the suggestion is to scan a column Sub CreateTaxCSVFile() Range("Start").Offset(1, 0).Select 'create starting point' Selection.CurrentRegion.Select 'to select a block of data' Function or call to Howmanytype3() ---------------------------------- With the current region selected, I need to have the function or sub to determine how many occurences of the numeric value 3 occurs in the first column of this selected region. The function needs to store that number of occurences as a value for use later on in the macro. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or a Sub?
Try this and amend
Dim msg As String Selection.CurrentRegion.Select ' optional, just need to select one cell msg = MsgBox(Selection.Address) msg = MsgBox(WorksheetFunction.CountIf(Range(Selection.A ddress), 3)) steve "Bob Phillips" wrote in message ... Bruce, This is a simple function to do it 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 Call it like so myNum = HowMany(Range("Start").Offset(1, 0).CurrentRegion,3) -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Bruce Roberson" wrote in message ... I'll try to describe what this is about, but in QPW this part was just a subroutine. This is the first part of a bunch of postings I'll need to do relating to creating some CSV files. The purpose of this routine or function depending on the suggestion is to scan a column Sub CreateTaxCSVFile() Range("Start").Offset(1, 0).Select 'create starting point' Selection.CurrentRegion.Select 'to select a block of data' Function or call to Howmanytype3() ---------------------------------- With the current region selected, I need to have the function or sub to determine how many occurences of the numeric value 3 occurs in the first column of this selected region. The function needs to store that number of occurences as a value for use later on in the macro. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function or a Sub?
Bruce,
As it turns out in this example range, there was only one occurrence of the value 3 in this column. I tested the watch by copying three more records in my data and then I put a watch on "Howmany" in debug mode. The value correctly showed 4 after I reran the macro. This was my first time to add a watch. So is that all there is to checking values of routines? Not all there is, but part of it. You can use watches, you can print out variables to the immediate window (Debug.Print myVar), you could write output to a logfile, step through the code, use conditional compilation, check the call stack, etc.. Lots of tools at your disposal, unfortunately never enough. Regards Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |