Been trying for days to write code that:

1. Searches for the first/next occurrence of both of 2 strings
in the combined text of two adjacent cells (A and B) in the
same row, within a specified 2-column range.

2. When a match is found, displays the combined A+B and prompts
the user to accept it (write it out to a text file) or look for
the next match.

3. Repeats Step 2 until user accepts the displayed A+B or until
no more matches are found at which point it should stop. It's
the stopping that's giving me the most trouble.

The code is such a mess now that I can't even make sense of it,
so I'm gonna start over in the morning. Meanwhile, does anyone
who has faced this chore before have any advice on structure?

Concatenating A and B beforehand is not an option because the
file grows extra records on the fly.

Thanks in advance for any clues at all.

--
Mark Tangard , Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters

Thanks, Tom, you've done almost all my work for me!

I did see the example in the help but this clarfified what
they meant there.

MT



Tom Ogilvy wrote:

If you have Excel 97 or Excel 2000, the help example on the Find Method of
the Range Object has an example of finding all instances of a search string.

I would search column A for the first target, then each time I found it,
look at the adjacent value in column B and see if it was the second target.
If not, continue to search, if so put up the dialog. Jump out of the loop
in response to your msgbox.

Here is a basic example of adapting that code:

Sub Tester1()
Dim c As Range
Dim bFound As Boolean
Dim firstAddress As String
Dim res As Variant
With Worksheets(1).Columns(1)
Set c = .Find("FirstString", _
After:=Range("A65536"), LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If LCase(c.Offset(0, 1).Value) = "secondstring" Then
c.Resize(1, 2).Select
res = MsgBox("Is this the row: " & c.Address, vbYesNo)
If res = vbYes Then
bFound = True
Exit Do
End If
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address < firstAddress
End If
If bFound Then _
MsgBox c.Address
End With
End Sub

--
Regards,
Tom Ogilvy

"Mark Tangard" wrote in message
...
Been trying for days to write code that:

1. Searches for the first/next occurrence of both of 2 strings
in the combined text of two adjacent cells (A and B) in the
same row, within a specified 2-column range.

2. When a match is found, displays the combined A+B and prompts
the user to accept it (write it out to a text file) or look for
the next match.

3. Repeats Step 2 until user accepts the displayed A+B or until
no more matches are found at which point it should stop. It's
the stopping that's giving me the most trouble.

The code is such a mess now that I can't even make sense of it,
so I'm gonna start over in the morning. Meanwhile, does anyone
who has faced this chore before have any advice on structure?

Concatenating A and B beforehand is not an option because the
file grows extra records on the fly.

Thanks in advance for any clues at all.

--
Mark Tangard , Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters

Mark,

untested code
sub Find_for_Mark()

dim string1, string2 as string

string1=inputbox("Enter String 1")
string2=inputbox("Enter String 2") ' or use whatever turns you on, read cell
values or convert to function with strings as parameters

range("A1").select
do until activecell.value="" 'stop at end of list
If activecell.value= string1 then
if activecell.offset(0,1).value=string2 then
response=msgbox("Accept this value?",vbYesNo)
If response = vbyes then
'do what you have to here
Exit Sub 'if you don't want to check more otherwise omit
End if
End if
End if
Activecell.offset(1,0).select
Loop
end sub

HTH

Steve

"Mark Tangard" wrote in message
...
Been trying for days to write code that:

1. Searches for the first/next occurrence of both of 2 strings
in the combined text of two adjacent cells (A and B) in the
same row, within a specified 2-column range.

2. When a match is found, displays the combined A+B and prompts
the user to accept it (write it out to a text file) or look for
the next match.

3. Repeats Step 2 until user accepts the displayed A+B or until
no more matches are found at which point it should stop. It's
the stopping that's giving me the most trouble.

The code is such a mess now that I can't even make sense of it,
so I'm gonna start over in the morning. Meanwhile, does anyone
who has faced this chore before have any advice on structure?

Concatenating A and B beforehand is not an option because the
file grows extra records on the fly.

Thanks in advance for any clues at all.

--
Mark Tangard , Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters