Basic Search with Wildcharacters
 

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

Basic Search with Wildcharacters
 

On Jul 31, 4:58 pm, wrote:
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

Basic Search with Wildcharacters
 

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
--
HTH...

Jim Thomlinson


" wrote:

On Jul 31, 4:58 pm, wrote:
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