Find with criteria

[Soniya]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Soniya]

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

 

[Bob Phillips]

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.