Searching for Single Words using macros

[supportuk1]

Can someone help?:

I have a table in excel with customer information in it, I want to be able to search for one word in a cell by using macros. I already have a Macros but I have to type in the full customer name.
Here is the macros I already have:



Sub Find_Data()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub

 

[supportuk1]

I am new to using Macros, I got this macros from another page on Mr Excel, I am searching for words. In the table, I have a column which is headline site, I have the customer name. The macros I have now is fine, it is search the word/s I am looking for, but it come up with 'value not found' but it has found it. There is something in the code that needs changing, that I need help with, so it only comes up if it doesn't actually find it.

[/Code] Sub Find_Data()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub [/code]

 

[Joe4]

In HTML, there are opening tags and closing tags. Only the closing tags have the backslash in front of the word. Every opening tag needs a closing tag and vice versa. If you look at the example that Jon posted in that link, he posted EXACTLY what it needs to look like.

The issue is this line here:

[COLOR=#333333]If ActiveCell.Value = datatoFind Then Exit Sub[/COLOR]

This is the line that tells it to bail out of the VBA before it gets to the error message. It is checking to see if the ActiveCell matches the value that we are looking for. But remember, we changed it so that it no longer is exactly equal to, but just contained in.

I have re-written the macro to work bit differently. I removed the parts you don't need, and changed the error handling. So, now, if it does not find the value (which normally resorts in an error), it tells you the value is not found.

Sub Find_Data()

    Dim datatoFind

    On Error GoTo err_chk
    datatoFind = InputBox("Please enter the value to search for")
    If datatoFind = "" Then Exit Sub

    Range("A1").Activate
    Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        
    On Error GoTo 0
    Exit Sub

    
err_chk:
    If Err.Number = 91 Then
        MsgBox ("Value not found")
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If

End Sub