Please help me making an excel coding Write a VBA program to count the number of
ID: 662865 • Letter: P
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.