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

On Jul 31, 4:58 pm, cubhas...@gmail.com 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

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


"(E-Mail Removed)" wrote:

> On Jul 31, 4:58 pm, cubhas...@gmail.com 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
>
>