Hi All,

Could someone pls help me on the following...

I have textbox1 and textbox2 in my userform.

my following code searches for the string and works fine.

I want to modify it to check one more thing..

it should find textbox1 text and if found match found row
column 6 = textbox2.text
else continue search until both condition is true

or the string is not found.

the data is ticket nos and if a ticket is refunded the
same number appears
twice (it may or may not be in same sheet)

column B is ticket numbers and Column F is " S" or "R" to
identify sales or
refund.

how cud i modify my code to find the data matching the
condition "S" or "R"
wich is in TextBox2 ?


Sub SearchTkt()
Application.ScreenUpdating = False
Sheets("Interface").Select
sStr = TextBox1.Text

For Each sh In ThisWorkbook.Worksheets
If sStr < "" Then
Set rng = Nothing

If Option1.Text = "TO/IOTR/XO" Then
Set rng = sh.Range("X:X").Find(What:="*" & sStr, _
After:=sh.Range("X1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Else

Set rng = sh.Range("B:B").Find(What:="*" & sStr, _
After:=sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End If

End If
If Not rng Is Nothing Then


TktNo.Text = rng.Text
IssDate.Text = sh.Cells(rng.Row(), 7).Text
Route.Text = sh.Cells(rng.Row(), 10).Text
PaxName.Text = sh.Cells(rng.Row(), 11).Text
PubFare.Text = sh.Cells(rng.Row(), 13).Text
ComFare.Text = sh.Cells(rng.Row(), 14).Text
Tax1.Text = sh.Cells(rng.Row(), 19).Text
Tax2.Text = sh.Cells(rng.Row(), 20).Text
Tax3.Text = sh.Cells(rng.Row(), 21).Text
FuelSur.Text = sh.Cells(rng.Row(), 22).Text
Fd.Text = sh.Cells(rng.Row(), 15).Text
Upd.Text = sh.Cells(rng.Row(), 17).Text
Rev.Text = sh.Cells(rng.Row(), 18).Text
Staff.Text = sh.Cells(rng.Row(), 23).Text
AddColl.Text = sh.Cells(rng.Row(), 25).Text
Stock.Text = sh.Cells(rng.Row(), 27).Text
SplCom.Text = sh.Cells(rng.Row(), 29).Text
Net2Air.Text = sh.Cells(rng.Row(), 30).Text
Cmbl.Text = sh.Cells(rng.Row(), 33).Text
SalRef.Text = sh.Cells(rng.Row(), 6).Text
XitNo.Text = sh.Cells(rng.Row(), 24).Text
Target.Text = sh.Cells(rng.Row(), 27).Text
Tkttyp.Text = sh.Cells(rng.Row(), 3).Text
CjV.Text = sh.Cells(rng.Row(), 35).Text

Exit Sub
End If

Next

If rng Is Nothing Then

LblMsg.Caption = Option1.Text & " No. " & sStr & " was
Not found"

End If
Sheets("Interface").Select
End Sub


Your help is highly appreciated...

Thanks again...

Soniya

Assuming max of two entries and if there are two, one will be S and one
will be R


If Option1.Text = "TO/IOTR/XO" Then
Set rng = sh.Range("X:X").Find(What:="*" & sStr, _
After:=sh.Range("X1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
if Ucase(sh.Cells(rng.row,"F")) < Ucase(Textbox2.Text) then
set rng = sh.Range("X:X").FindNext(rng)
end if
end if

Else

Set rng = sh.Range("B:B").Find(What:="*" & sStr, _
After:=sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

if not rng is nothing then
if Ucase(sh.Cells(rng.row,"F")) < Ucase(Textbox2.Text) then
set rng = sh.Range("B:B").FindNext(rng)
end if
end if

End If

--
Regards,
Tom Ogilvy

"Soniya" wrote in message
...
Hi All,

Could someone pls help me on the following...

I have textbox1 and textbox2 in my userform.

my following code searches for the string and works fine.

I want to modify it to check one more thing..

it should find textbox1 text and if found match found row
column 6 = textbox2.text
else continue search until both condition is true

or the string is not found.

the data is ticket nos and if a ticket is refunded the
same number appears
twice (it may or may not be in same sheet)

column B is ticket numbers and Column F is " S" or "R" to
identify sales or
refund.

how cud i modify my code to find the data matching the
condition "S" or "R"
wich is in TextBox2 ?


Sub SearchTkt()
Application.ScreenUpdating = False
Sheets("Interface").Select
sStr = TextBox1.Text

For Each sh In ThisWorkbook.Worksheets
If sStr < "" Then
Set rng = Nothing

If Option1.Text = "TO/IOTR/XO" Then
Set rng = sh.Range("X:X").Find(What:="*" & sStr, _
After:=sh.Range("X1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Else

Set rng = sh.Range("B:B").Find(What:="*" & sStr, _
After:=sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End If

End If
If Not rng Is Nothing Then


TktNo.Text = rng.Text
IssDate.Text = sh.Cells(rng.Row(), 7).Text
Route.Text = sh.Cells(rng.Row(), 10).Text
PaxName.Text = sh.Cells(rng.Row(), 11).Text
PubFare.Text = sh.Cells(rng.Row(), 13).Text
ComFare.Text = sh.Cells(rng.Row(), 14).Text
Tax1.Text = sh.Cells(rng.Row(), 19).Text
Tax2.Text = sh.Cells(rng.Row(), 20).Text
Tax3.Text = sh.Cells(rng.Row(), 21).Text
FuelSur.Text = sh.Cells(rng.Row(), 22).Text
Fd.Text = sh.Cells(rng.Row(), 15).Text
Upd.Text = sh.Cells(rng.Row(), 17).Text
Rev.Text = sh.Cells(rng.Row(), 18).Text
Staff.Text = sh.Cells(rng.Row(), 23).Text
AddColl.Text = sh.Cells(rng.Row(), 25).Text
Stock.Text = sh.Cells(rng.Row(), 27).Text
SplCom.Text = sh.Cells(rng.Row(), 29).Text
Net2Air.Text = sh.Cells(rng.Row(), 30).Text
Cmbl.Text = sh.Cells(rng.Row(), 33).Text
SalRef.Text = sh.Cells(rng.Row(), 6).Text
XitNo.Text = sh.Cells(rng.Row(), 24).Text
Target.Text = sh.Cells(rng.Row(), 27).Text
Tkttyp.Text = sh.Cells(rng.Row(), 3).Text
CjV.Text = sh.Cells(rng.Row(), 35).Text

Exit Sub
End If

Next

If rng Is Nothing Then

LblMsg.Caption = Option1.Text & " No. " & sStr & " was
Not found"

End If
Sheets("Interface").Select
End Sub


Your help is highly appreciated...

Thanks again...

Soniya