Please help me making an excel coding Write a VBA program to count the number of

Question

Please help me making an excel coding

Write a VBA program to count the number of occurrences of an integer or letter within a larger string (Hint: use a loop along with the LEN and MID functions.) Use one input box to enter a string of arbitrary length, and another input box to enter the value to search for. Then, use a Message Box to display the number of times the search value occurs within the longer string. For example, if the long string is

abccccdghcmkncf

and the search value is c, the answer will be 6.

Explanation / Answer

Create two text boxes and a command button. In the command_click event call the function using the folllowing code:

Private Sub CommandButton1_Click()
Dim occ As Long
occ = FindOccur(TextBox1.Text, TextBox2.Text)
MsgBox occ
End Sub

Enter the following function code in the General ( Declaration Section):

Function FindOccur(T1 As String, T2 As String) As Long
Dim Position As Long
Dim X As Long
Dim Count As Long
    If Len(T1) = 0 Then Exit Function
    If Len(T2) = 0 Then Exit Function
    Position = 1
    Do
        Position = InStr(Position, T1, T2)
        X = Position
        If Position > 0 Then
            Count = Count + 1
            Position = Position + Len(T2)
        End If
    Loop Until Position = 0
    FindOccur = Count
End Function

Enter the text to search in the second text box and main string in the first text box. Thereafter, click the command button. A messagebox shows the number of occurrences of the character or number entered in the second text box as it occurs in the string in the first text box.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.