|
Search and match condition
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 |
Search and match condition
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 |
| All times are GMT +1. The time now is 10:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com