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

 

[Joe4]

Welcome to the Board!

You have to set it having to complete an exact/complete match, instead of just finding what you are looking for anywhere in the cell.

Change this argument:

LookAt:=xlWhole

to this:

LookAt:=xlPart

 

[supportuk1]

Thank you for replying.
Where about in the Macros do I put LookAt:=xlPart

 

[Joe4]

Right where you have the LookAt:=xlWhole now. Maybe you are missing it because you have it split over two lines:

[COLOR=#808080]Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, [/COLOR][B][COLOR=#ff0000]LookAt _
:=xlWhole[/COLOR][/B][COLOR=#808080], SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _[/COLOR]

Just change the word xlWhole to xlPart.

 

[supportuk1]

I know have the macros below, it has worked but comes up with a message saying it hasn't found the name but it has, how do I get that to stop?



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

 

[Joe4]

Can you try re-posting your code using Code Tags (see: https://www.mrexcel.com/forum/board...st-your-visual-basic-applications-code.html)?

Reading through unformatted code is a bit ugly and cumbersome.

 

[supportuk1]

My computer will not allow this happen I will try make it more clearer for you.

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

 

[Joe4]

My computer will not allow this happen I will try make it more clearer for you.

Really? It won't allow you to type a left bracket, the word CODE and then the right bracket?

Can you lay out an example for us, so we can try running your code against a situation similar to what you have?
Please provide:
- The number of sheets in your workbook
- The value you are searching for
- The location of some matches (which sheet/cell)
- The complete string of the match (since we are doing partial matches)

 

[supportuk1]

(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)

1. we have 1 work sheet
2. Site
3. Sheet 1
4. Table A6: G72

 

[Joe4]

You want to use square brackets, i.e. [ and ]
You put the word code between the square brackets for the opening tag, and /code between the square brackets for the closing tag.
See the first example in that link I pointed you to. That is how you want them to look before posting.

1. we have 1 work sheet
2. Site
3. Sheet 1
4. Table A6: G72

Not sure why you are looping through sheets if you only have one sheet. That loop seems unnecessary then.

I think you may have misunderstood part of my last question. I don't think you are looking for the word "Site" in the phrase "Sheet 1".
One of the things I was trying to ask you what word you looking for. And then, in the cell that you find it in, what is the complete entry in that cell?
For example, maybe you are searching for the word "dog" and in cell B14 you have the entry "I own a dog".

I was a bit confused as to whether or not you are searching for words or numbers, as you seem to be using numeric conversion functions like "Cdbl".
Did you actually write the code yourself, or just get it from somewhere else and just trying to retro-fit it for your purposes?