Does this mean that you have your solution? I'm kind of confused.

If you need more help, what do you want to do when you find your string?



Jeff wrote:

Messed up in typing this out

'This is basically what I've did without using 'Find'
Figuring out how to use 'Find' can be a pain.
Var = "Blackberry"
SearchString = "blackberry"

Set rng = Range("G2", Cells(Rows.Count, "G").End(xlUp))

For Each c In rng
Str1 = InStr(1, c.Value, Var)
Str2 = InStr(1, c.Value, SearchString)
If Str1 < 0 Or Str2 < 0 then
'if Str is not zero then
'return all infor in cell to a worksheet
end if
Next

--
Jeff

"Dave Peterson" wrote:

My next question is to ask if you need to find BlAcKbErRy, too?

If you're trying to find all occurrences of blackberry without looking at case,
then it gets simpler. You don't need to look twice--you just need to change the
..find to ignore case (matchcase:=false).

Option Explicit
Sub testme()

Dim FirstWord As String
Dim SecondWord As String
Dim FirstAddress As String
Dim FoundCell As Range
Dim FoundRng As Range
Dim RngToSearch As Range
Dim myCell As Range

FirstWord = "Blackberry"
SecondWord = LCase(FirstWord) 'is this ok

With Worksheets("Input data")
Set RngToSearch = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))
End With

With RngToSearch
Set FoundCell = .Cells.Find(what:=FirstWord, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=FALSE) '<--- biggest change

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
'do the work here
'or build a range of all the cells???

If FoundRng Is Nothing Then
Set FoundRng = FoundCell
Else
Set FoundRng = Union(FoundRng, FoundCell)
End If

Set FoundCell = .FindNext(after:=FoundCell)

If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If

If FoundRng Is Nothing Then
MsgBox "Not found!"
Else
For Each myCell In FoundRng.Cells
MsgBox myCell.Address(0, 0)
'or do what you want to each of them
Next myCell
End If
End With

End Sub

Depending on what you're doing, you could drop the second loop and just modify
the first to find the info and work on it.



Jeff wrote:

Hi Dave

My fault... I was not correct in this statement.
"I need to Find two matches in a cell."

I should have stated the following
"I need to Find either or both matches in a cell."

Thank you for helping...
Taking your time to write out this code is appreciated.
It's very clever...

Jeff :-)

--
Jeff

"Dave Peterson" wrote:

Don't do a second find. Just use instr() and look for a case sensitive match.

If you find the lower case version, then do what you want, else do another find.

Option Explicit
Sub testme()

Dim FirstWord As String
Dim SecondWord As String
Dim FirstAddress As String
Dim FoundCell As Range
Dim FoundRng As Range
Dim RngToSearch As Range
Dim myCell As Range

FirstWord = "Blackberry"
SecondWord = LCase(FirstWord) 'is this ok

With Worksheets("Input data")
Set RngToSearch = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))
End With

With RngToSearch
Set FoundCell = .Cells.Find(what:=FirstWord, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=True)

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
'really look at formulas? Not .value???
If InStr(1, FoundCell.Formula, SecondWord, _
vbBinaryCompare) 0 Then
If FoundRng Is Nothing Then
Set FoundRng = FoundCell
Else
Set FoundRng = Union(FoundRng, FoundCell)
End If
End If
Set FoundCell = .FindNext(after:=FoundCell)

If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If

If FoundRng Is Nothing Then
MsgBox "Not found!"
Else
For Each myCell In FoundRng.Cells
MsgBox myCell.Address(0, 0)
'or do what you want to each of them
Next myCell
End If
End With

End Sub




Jeff wrote:

capitalization Find.

SearchString1 = Blackberry
SearchString2 = blackberry

Please Help. :-)

I need to Find two matches in a cell.
I need to find the first and second searchstring in a cell,
then move move down to the next cell and do it all over
again for the next cell in the column.

The problem is the second SearchString2 starts a find
all over again from the first word in the cell.

'====================================

Set rngToSearch = Sheets("Input data").Columns("G")
On Error Resume Next
Set rng = Range("G2", Cells(Rows.Count, "G").End(xlUp))
Set rngFound = rngToSearch.Find(What:=SearchString1, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
On Error GoTo 0
For Each c In rng
'find first SearchString1
For Each c in rng
Set rngFound = rngToSearch.Find(What:=SearchString2, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
'find second SearchString2
Next c
Next c

'=================================

you help is very much appreciated

Jeff
--
Jeff

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson