Basic Search with Wildcharacters

[cubhasker]

Below is a Sub I have written. I have huge data in Sheet3 with
different forms of names containing "shell". Whenever I encounter
shell in column A of Sheet3 I should write SHELL in column F

Sub NameBusiness()
Dim srchResult As Integer
Dim temp As String
temp = "search(""*shell*"",sheet3!a2,1)"

For i = 2 To 59998
srchResult = ActiveSheet.Evaluate(temp)

If (srchResult = 1) = True Then
Sheet3.Cells(i, 6) = "Exxon Mobil"
End If

Next i


End Sub

 

[cubhasker]

Below is a Sub I have written. I have huge data in Sheet3 with
different forms of names containing "shell". Whenever I encounter
shell in column A of Sheet3 I should write SHELL in column F

Sub NameBusiness()
Dim srchResult As Integer
Dim temp As String
temp = "search(""*shell*"",sheet3!a2,1)"

For i = 2 To 59998
srchResult = ActiveSheet.Evaluate(temp)

If (srchResult = 1) = True Then
Sheet3.Cells(i, 6) = "Exxon Mobil"
End If

Next i

End Sub

Sorry.. forgot to mention the error I am getting.It throws me a Type
Mismatch error.

Thanks in advance

 

[Guest]

Instead of Evaluate you would be better off with InStr. That being said Find
will be a much more efficient way to do this...

Sub NameBusiness()
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngToSearch = Sheet3.Columns(6)
Set rngFound = rngToSearch.Find(What:="shell", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
rngFound.Offset(0, 1).Value = "Shell"
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub