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.

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.

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.

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