Link to home
Start Free TrialLog in
Avatar of Alex Campbell
Alex CampbellFlag for United States of America

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.

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 

Open in new window

Avatar of Rgonzo1971
Rgonzo1971

Hi,

A simple version would look like this (without error handling or plural /singular and so on)

Function Target_Count(Target As String, rng As Range)
   Count = 0
   If Target = "" Then GoTo Done
      For Each c In rng
         N = InStr(1, c.Value, Target)
         While N <> 0
            Count = Count + 1
            N = InStr(N + 1, c.Value, Target)
         Wend
      Next c
  Target_Count = Count & " Occurrences of " & Target
Done:
End Function

Open in new window

Regards
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

Avatar of Alex Campbell

ASKER

Matches macro just as I asked. Thanks