Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Basic File Search" | Excel Discussion (Misc queries) | |||
Why can't I do a "basic search" of my computer in Excel 2007 | New Users to Excel | |||
Can I run Visual Basic procedure using Excel Visual Basic editor? | Excel Programming | |||
Create a search Field within a worksheet to search command buttons | Excel Programming | |||
Problems in Visual Basic Editior, In search of a command | Excel Programming |