Message number found

Posted by Paul on April 16, 2001 1:35 PM

Is it possible to write a macro that searchs a coloumn for what has been entered into an input box and then displays the number of times it is found in a message box or simiilar?
Thanks

Posted by Dave Hawley on April 16, 2001 1:45 PM

Hi Paul

What do you mean by "Input Box" ?

Dave

OzGrid Business Applications

Posted by Bj on April 16, 2001 2:20 PM

Try putting this into a commandbutton... I bet Dave has a cleaner way.

Private Sub CommandButton1_Click()
Dim MyVar As String
Dim MyAns As Variant
Dim LastRow As Integer
Dim X As Integer
Dim Y As Integer

Y = 0

MyVar = Application.InputBox("What are you looking for?")

LastRow = Range("A65536").End(xlUp).Row
For X = 1 To LastRow
If Range("B" & X).Value = MyVar Then 'Change B to correct column
Y = Y + 1
End If

Next X

If Y = 1 Then
MsgBox Y & " occurrence"
Else
MsgBox Y & " occurrences"
End If

End Sub

Posted by Paul on April 16, 2001 2:21 PM

e.g. Application.InputBox("Your name", _
"Name", , , , , 2)

Posted by Dave Hawley on April 16, 2001 2:28 PM

Next X MsgBox Y & " occurrence" Else MsgBox Y & " occurrences"

Thanks to Bj I now understand! here is another method:

Sub CellContents()
'Writtent By OzGrid Business Applications
'www.ozgrid.com
'Finds and Displays what is entered into input box

Dim Reply As String, Answer As String

Reply = InputBox("What are you looking for in Column " _
& ActiveCell.Column, "Find and Count")
If Reply = "" Then Exit Sub

Answer = WorksheetFunction.CountIf(ActiveCell.EntireColumn, Reply)

MsgBox "There are " & Answer & " occurences of " _
& Reply & " in Column " & ActiveCell.Column, vbInformation
End Sub

Dave

OzGrid Business Applications