VBA counting Function 1

[Schmeggie]

I am trying to write a new function in VBA for excel that will count the occurrences of specific alpha-numeric codes in a column. There are so many variables that the regular excel formulas cannot handle the calculations.

The regular formula would look similar to this:

=sum(countif(Day,{"A01","A02","A03","A04","A05","B01","B02","B12","B13","B14",
"B15","C14","C17","C18","C19"}))

Day is already a defined range - basically a column C24:c195 and is a relative range dependent on the cell location of the function. There are over 60 alpha-numeric codes to be counted, I did not add them all here to save time.

I would call the function "Shift0700".

Any help would be great.

thanks,

 

[AnotherHiggins]

First, DAY is a reserved word in Excel - it's a function. So you should definitely change the name of that Named Range.

You can easily accomplish your goal without VBA....

Looking at your example, place your 15 criteria in A1:A15. Let's say the cells you're searching are in C24:C195.

In another cell, type in the following Array Formula:
[tab][COLOR=blue white]=SUM(COUNTIF($C$24:$C$195, "*" & $A$1:$A$15& "*"))[/color]
And - this is critical - enter with [!][Ctrl] + [Shift] + [Enter][/!] instead of just [Enter]. If you do this correctly, the entire formula will be wrapped in curly brackets, looking like this:
[tab]={SUM(COUNTIF($C$24:$C$195, "*" & $A$1:$A$15& "*"))}
Bear in mind that you DO NOT place the curly brackets there manually, they'll appear after you press [Ctrl] + [Shift] + [Enter].

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[combo]

I would use defined range as input range anyway. The UDF could be:

Public Function Shift700(InputRange As Range)
Dim Codes(1 To 60)
Codes(1) = "A01": Codes(2) = "A02" ' etc
Shift700 = 0
For Each c In InputRange
    For i = 1 To 60
        If c.Value = Codes(i) Then
            Shift700 = Shift700 + 1
            Exit For
        End If
    Next i
Next c
End Function

combo