Counting | using VBA

[Stephen_IV]

I have benn trying to figure this out but to no avail. I know that you can use the split function but I am stuck. Can anyone please help me out???

Thanks in advance Stephen

Book1
	A	B	C	D
1	9|10|11|12|1	5
2	11|12|1|2|3|4	6
Sheet1

 

[Cbrine]

Add this code to a module in your workbook. Then call it as a formula in the workbook.

=AddPipe("A1")

Function AddPipe(Target As Range)
Dim C As Characters, Count As Integer
Dim Counter As Integer
Count = 1
For Counter = 1 To Target.Characters.Count
    If Mid(Target, Counter, 1) = "|" Then
        Count = Count + 1
    End If
Next Counter
AddPipe = Count
End Function

HTH
Cal

 

[Stephen_IV]

Cbrine,

Thanks for your response. The function works fine except if there is not a pipe in the cell if I have a 9 or 4 etc. Is there a way to return blank instead of the #VALUE! error??

Thanks again Stephen

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
    MsgBox Len([A1]) - Len(.Substitute([A1] & "|", "|", "")) + 1
    MsgBox Len([A2]) - Len(.Substitute([A2] & "|", "|", "")) + 1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Cbrine]

ADVERTISEMENT

Here's some modified code that works with single values and blanks

Function AddPipe(Target As Range)
Dim C As Characters, Count As Integer

Dim Counter As Integer

If Len(Target) = 0 Then
    AddPipe = 0
    Exit Function
End If
Count = 1

For Counter = 1 To Len(Target)
    If Mid(Target, Counter, 1) = "|" Then
        Count = Count + 1
    End If
Next Counter

AddPipe = Count

End Function

HTH
Cal

 

[Stephen_IV]

Cbrine,

Thanks that did the trick! Thanks you also just_jon for your response!!

Stephen

 

[Cbrine]

ADVERTISEMENT

I like your idea better Jon. Much more effieicent idea for counting the instances.

Cal

 

[just_jon]

I like your idea better Jon. Much more effieicent idea for counting the instances.

Cal

Combining w/ your Function -

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> AddPipe(Target <SPAN style="color:#00007F">As</SPAN> Range)

<SPAN style="color:#00007F">If</SPAN> Len(Target) = 0 <SPAN style="color:#00007F">Then</SPAN>
    AddPipe = 0
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
    AddPipe = Len([Target]) - Len(.Substitute([Target] & "|", "|", "")) + 1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

 

[Greg Truby]

I can only assume that this is a homework assignment since one would normally solve this problem using the standard formula that Jon wrapped in VBA as a UDF, ie. <ul>[*]=LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))[/list]So if it is homework, why we'd certainly want us to get an "A" on it :wink:, so here's how to use the split function in VBA...

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> pipesplitter(rngInput <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#007F00">' returns #Ref! error if more than one cell</SPAN>
    <SPAN style="color:#007F00">' returns -1 if cell is blank</SPAN>
    <SPAN style="color:#00007F">If</SPAN> rngInput.Count > 1 <SPAN style="color:#00007F">Then</SPAN>
        pipesplitter = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrRef)
    <SPAN style="color:#00007F">Else</SPAN>
        pipesplitter = <SPAN style="color:#00007F">UBound</SPAN>(Split(rngInput, "|"))
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

Or am I mistaken and we're not being graded on this??

 

[Cbrine]

Or am I mistaken and we're not being graded on this??

I can't think of any productive uses for a UDF, code, or a function that does as the OP wants, but you never can tell.