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