|
Find with criteria
Hi All,
I have the follwing code to search in all sheets in my current workbook. (1) Can I add a crieteria in the search? if the item is foud data is displayed in my UserForm. something like: istead of "If Not rng Is Nothing then" can i have "If Not rng Is Nothing And rng.offset(0,5)="S" Then" (2) In My data display code I have IssDate.Text = rng.Offset(0, 5).Text Instead of the rng.offset can I use (row,col) so i can avoid repeating the code for the if else ? in the firast case it is based on column B and second case based on column X. if I use current row column 1,2,3 etc i can avoid repeating the code. but How? Sub SearchTkt() Application.ScreenUpdating = False sStr = ToFind.Text For Each sh In ThisWorkbook.Worksheets If sStr < "" Then Set rng = Nothing If Option1.Text = "A" Then Set rng = sh.Range("X:X").Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ElseIf Option1.Text = "B" Then 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 If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text PaxName.Text = rng.Offset(0, 9).Text PubFare.Text = rng.Offset(0, 11).Text ComFare.Text = rng.Offset(0, 12).Text Tax1.Text = rng.Offset(0, 17).Text Tax2.Text = rng.Offset(0, 18).Text Tax3.Text = rng.Offset(0, 19).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text PaxName.Text = rng.Offset(0, -13).Text PubFare.Text = rng.Offset(0, -11).Text ComFare.Text = rng.Offset(0, -10).Text Tax1.Text = rng.Offset(0, -5).Text Tax2.Text = rng.Offset(0, -4).Text Tax3.Text = rng.Offset(0, -3).Text End If Exit Sub End If Next If rng Is Nothing Then LblMsg.Caption = Option1.Text & " No. " & sStr & " was Not found" End If TIA Soniya |
Find with criteria
Morning SOniya,
1) No you can't do it that way because VBA evaluates all parts of the If statement, so if the rng is nothing, it still does the other check, which bombs. What you need is If Not rng Is Nothing Then If rng.Offset(0, 5) = "S" Then 2) Try If Option1.Text = "A" Then Set rng = sh.Range("X:X") Else Set rng = sh.Range("B:B") End If rng.Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) -- HTH RP "Soniya" wrote in message ... Hi All, I have the follwing code to search in all sheets in my current workbook. (1) Can I add a crieteria in the search? if the item is foud data is displayed in my UserForm. something like: istead of "If Not rng Is Nothing then" can i have "If Not rng Is Nothing And rng.offset(0,5)="S" Then" (2) In My data display code I have IssDate.Text = rng.Offset(0, 5).Text Instead of the rng.offset can I use (row,col) so i can avoid repeating the code for the if else ? in the firast case it is based on column B and second case based on column X. if I use current row column 1,2,3 etc i can avoid repeating the code. but How? Sub SearchTkt() Application.ScreenUpdating = False sStr = ToFind.Text For Each sh In ThisWorkbook.Worksheets If sStr < "" Then Set rng = Nothing If Option1.Text = "A" Then Set rng = sh.Range("X:X").Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ElseIf Option1.Text = "B" Then 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 If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text PaxName.Text = rng.Offset(0, 9).Text PubFare.Text = rng.Offset(0, 11).Text ComFare.Text = rng.Offset(0, 12).Text Tax1.Text = rng.Offset(0, 17).Text Tax2.Text = rng.Offset(0, 18).Text Tax3.Text = rng.Offset(0, 19).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text PaxName.Text = rng.Offset(0, -13).Text PubFare.Text = rng.Offset(0, -11).Text ComFare.Text = rng.Offset(0, -10).Text Tax1.Text = rng.Offset(0, -5).Text Tax2.Text = rng.Offset(0, -4).Text Tax3.Text = rng.Offset(0, -3).Text End If Exit Sub End If Next If rng Is Nothing Then LblMsg.Caption = Option1.Text & " No. " & sStr & " was Not found" End If TIA Soniya |
Find with criteria
Thanks Bob,
the second paert u missed i think.. since i have that code already.. my concern is here.. If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text endif in both case option1="A" and "B" rng.offset(0,5) and rng.offset(0,-22) refers the same cell. Can I reference it in another way so in both cases the code will be same.. TIA Soniya -----Original Message----- Morning SOniya, 1) No you can't do it that way because VBA evaluates all parts of the If statement, so if the rng is nothing, it still does the other check, which bombs. What you need is If Not rng Is Nothing Then If rng.Offset(0, 5) = "S" Then 2) Try If Option1.Text = "A" Then Set rng = sh.Range("X:X") Else Set rng = sh.Range("B:B") End If rng.Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) -- HTH RP "Soniya" wrote in message ... Hi All, I have the follwing code to search in all sheets in my current workbook. (1) Can I add a crieteria in the search? if the item is foud data is displayed in my UserForm. something like: istead of "If Not rng Is Nothing then" can i have "If Not rng Is Nothing And rng.offset(0,5)="S" Then" (2) In My data display code I have IssDate.Text = rng.Offset(0, 5).Text Instead of the rng.offset can I use (row,col) so i can avoid repeating the code for the if else ? in the firast case it is based on column B and second case based on column X. if I use current row column 1,2,3 etc i can avoid repeating the code. but How? Sub SearchTkt() Application.ScreenUpdating = False sStr = ToFind.Text For Each sh In ThisWorkbook.Worksheets If sStr < "" Then Set rng = Nothing If Option1.Text = "A" Then Set rng = sh.Range("X:X").Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ElseIf Option1.Text = "B" Then 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 If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text PaxName.Text = rng.Offset(0, 9).Text PubFare.Text = rng.Offset(0, 11).Text ComFare.Text = rng.Offset(0, 12).Text Tax1.Text = rng.Offset(0, 17).Text Tax2.Text = rng.Offset(0, 18).Text Tax3.Text = rng.Offset(0, 19).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text PaxName.Text = rng.Offset(0, -13).Text PubFare.Text = rng.Offset(0, -11).Text ComFare.Text = rng.Offset(0, -10).Text Tax1.Text = rng.Offset(0, -5).Text Tax2.Text = rng.Offset(0, -4).Text Tax3.Text = rng.Offset(0, -3).Text End If Exit Sub End If Next If rng Is Nothing Then LblMsg.Caption = Option1.Text & " No. " & sStr & " was Not found" End If TIA Soniya . |
Find with criteria
thanks bob,
the second paert u missed i think.. since i have that code already.. my concern is here.. If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text endif in both case option1="A" and "B" rng.offset(0,5) and rng.offset(0,-22) refers the same cell. Can I reference it in another way so in both cases the code will be same.. TIA Soniya -----Original Message----- Thanks Bob, the second paert u missed i think.. since i have that code already.. my concern is here.. If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text endif in both case option1="A" and "B" rng.offset(0,5) and rng.offset(0,-22) refers the same cell. Can I reference it in another way so in both cases the code will be same.. TIA Soniya -----Original Message----- Morning SOniya, 1) No you can't do it that way because VBA evaluates all parts of the If statement, so if the rng is nothing, it still does the other check, which bombs. What you need is If Not rng Is Nothing Then If rng.Offset(0, 5) = "S" Then 2) Try If Option1.Text = "A" Then Set rng = sh.Range("X:X") Else Set rng = sh.Range("B:B") End If rng.Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) -- HTH RP "Soniya" wrote in message ... Hi All, I have the follwing code to search in all sheets in my current workbook. (1) Can I add a crieteria in the search? if the item is foud data is displayed in my UserForm. something like: istead of "If Not rng Is Nothing then" can i have "If Not rng Is Nothing And rng.offset(0,5)="S" Then" (2) In My data display code I have IssDate.Text = rng.Offset(0, 5).Text Instead of the rng.offset can I use (row,col) so i can avoid repeating the code for the if else ? in the firast case it is based on column B and second case based on column X. if I use current row column 1,2,3 etc i can avoid repeating the code. but How? Sub SearchTkt() Application.ScreenUpdating = False sStr = ToFind.Text For Each sh In ThisWorkbook.Worksheets If sStr < "" Then Set rng = Nothing If Option1.Text = "A" Then Set rng = sh.Range("X:X").Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ElseIf Option1.Text = "B" Then 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 If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text PaxName.Text = rng.Offset(0, 9).Text PubFare.Text = rng.Offset(0, 11).Text ComFare.Text = rng.Offset(0, 12).Text Tax1.Text = rng.Offset(0, 17).Text Tax2.Text = rng.Offset(0, 18).Text Tax3.Text = rng.Offset(0, 19).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text PaxName.Text = rng.Offset(0, -13).Text PubFare.Text = rng.Offset(0, -11).Text ComFare.Text = rng.Offset(0, -10).Text Tax1.Text = rng.Offset(0, -5).Text Tax2.Text = rng.Offset(0, -4).Text Tax3.Text = rng.Offset(0, -3).Text End If Exit Sub End If Next If rng Is Nothing Then LblMsg.Caption = Option1.Text & " No. " & sStr & " was Not found" End If TIA Soniya . . |
Find with criteria
You've lost me now. You asked how to stop repeating code, now you say you
already have that. Highlight the code you want not repeated. -- HTH RP "Soniya" wrote in message ... thanks bob, the second paert u missed i think.. since i have that code already.. my concern is here.. If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text endif in both case option1="A" and "B" rng.offset(0,5) and rng.offset(0,-22) refers the same cell. Can I reference it in another way so in both cases the code will be same.. TIA Soniya -----Original Message----- Thanks Bob, the second paert u missed i think.. since i have that code already.. my concern is here.. If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text endif in both case option1="A" and "B" rng.offset(0,5) and rng.offset(0,-22) refers the same cell. Can I reference it in another way so in both cases the code will be same.. TIA Soniya -----Original Message----- Morning SOniya, 1) No you can't do it that way because VBA evaluates all parts of the If statement, so if the rng is nothing, it still does the other check, which bombs. What you need is If Not rng Is Nothing Then If rng.Offset(0, 5) = "S" Then 2) Try If Option1.Text = "A" Then Set rng = sh.Range("X:X") Else Set rng = sh.Range("B:B") End If rng.Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) -- HTH RP "Soniya" wrote in message ... Hi All, I have the follwing code to search in all sheets in my current workbook. (1) Can I add a crieteria in the search? if the item is foud data is displayed in my UserForm. something like: istead of "If Not rng Is Nothing then" can i have "If Not rng Is Nothing And rng.offset(0,5)="S" Then" (2) In My data display code I have IssDate.Text = rng.Offset(0, 5).Text Instead of the rng.offset can I use (row,col) so i can avoid repeating the code for the if else ? in the firast case it is based on column B and second case based on column X. if I use current row column 1,2,3 etc i can avoid repeating the code. but How? Sub SearchTkt() Application.ScreenUpdating = False sStr = ToFind.Text For Each sh In ThisWorkbook.Worksheets If sStr < "" Then Set rng = Nothing If Option1.Text = "A" Then Set rng = sh.Range("X:X").Find(What:=sStr, _ After:=sh.Range("X1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ElseIf Option1.Text = "B" Then 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 If Option1.Text = "A" Then TktNo.Text = rng.Text IssDate.Text = rng.Offset(0, 5).Text Route.Text = rng.Offset(0, 8).Text PaxName.Text = rng.Offset(0, 9).Text PubFare.Text = rng.Offset(0, 11).Text ComFare.Text = rng.Offset(0, 12).Text Tax1.Text = rng.Offset(0, 17).Text Tax2.Text = rng.Offset(0, 18).Text Tax3.Text = rng.Offset(0, 19).Text ElseIf Option1.Text = "B" Then TktNo.Text = rng.Offset(0, -22).Text 'Text IssDate.Text = rng.Offset(0, -17).Text Route.Text = rng.Offset(0, -14).Text PaxName.Text = rng.Offset(0, -13).Text PubFare.Text = rng.Offset(0, -11).Text ComFare.Text = rng.Offset(0, -10).Text Tax1.Text = rng.Offset(0, -5).Text Tax2.Text = rng.Offset(0, -4).Text Tax3.Text = rng.Offset(0, -3).Text End If Exit Sub End If Next If rng Is Nothing Then LblMsg.Caption = Option1.Text & " No. " & sStr & " was Not found" End If TIA Soniya . . |
| All times are GMT +1. The time now is 05:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com