Alex Campbell
asked on
Convert Macro to Function
While I know there are easier ways to count the number of occurrences of a particular character in a cell, I am learning VBA so I want to take a different approach.
The following is a macro from microsoft.com that prompts you for a character and then it returns how many times that character occurred in a cell or range of cells.
How would you convert this macro into a function that you would give a cell or range of cells and the character to be searched for and it would return the number of occurrences.
http://support.microsoft.com/kb/89794
The following Visual Basic procedure prompts you for a character (or characters), then searches through the currently selected cell, or range of cells, and displays a message box showing the total number of occurrences of that character or character string. This works for all alphanumeric characters.
The following is a macro from microsoft.com that prompts you for a character and then it returns how many times that character occurred in a cell or range of cells.
How would you convert this macro into a function that you would give a cell or range of cells and the character to be searched for and it would return the number of occurrences.
http://support.microsoft.com/kb/89794
The following Visual Basic procedure prompts you for a character (or characters), then searches through the currently selected cell, or range of cells, and displays a message box showing the total number of occurrences of that character or character string. This works for all alphanumeric characters.
Dim Count As Integer
Dim Target As String
Dim Cell As Object
Dim N As Integer
Sub Target_Count()
Count = 0
Target = InputBox("character(s) to find?")
If Target = "" Then GoTo Done
For Each Cell In Selection
N = InStr(1, cell.Value, target)
While N <> 0
Count = count + 1
N = InStr(n + 1, cell.Value, target)
Wend
Next Cell
MsgBox count & " Occurrences of " & target
Done:
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just as an alternative:
Function Target_Count(rData As Range, Target As String) As Long
Dim rCell As Range
Dim lCounter As Long
lCounter = 0
If Len(Target) > 0 Then
For Each rCell In rData.Cells
lCounter = lCounter + Len(rCell.Value) - Len(Replace(rCell.Value, Target, ""))
Next rCell
End If
Target_Count = lCounter
End Function
here's one from me:
Function CountNumbersOccurence(LookFor As String, TheRange As Range) As Long
Dim xx
Dim yy
Dim x
yy = TheRange.Value
For Each x In yy
xx = Split(x, "b")
CountNumbersOccurence = CountNumbersOccurence + UBound(xx)
Next x
End Function
Change to mine to account for target being more than one letter:
Function Target_Count(rData As Range, Target As String) As Long
Dim rCell As Range
Dim lCounter As Long
lCounter = 0
If Len(Target) > 0 Then
For Each rCell In rData.Cells
lCounter = lCounter + (Len(rCell.Value) - Len(Replace(rCell.Value, Target, ""))) / Len(Target)
Next rCell
End If
Target_Count = lCounter
End Function
oops got distracted , mine should be:
Function CountNumbersOccurence(LookFor As String, TheRange As Range) As Long
Dim xx
Dim yy
Dim x
yy = TheRange.Value
For Each x In yy
xx = Split(x,LookFor)
CountNumbersOccurence = CountNumbersOccurence + UBound(xx)
Next x
End Function
ASKER
Matches macro just as I asked. Thanks
A simple version would look like this (without error handling or plural /singular and so on)
Open in new window
Regards