Hi All,

I'm a baby to Excel Programming.

But I've to programme Excel 2003 work book in order that if sth is typed in
the text box control. The code searches all the worksheets and displays the
value in the label control.
I've a textbox control, label control and a command button control in Sheet 1

Now with the below mentioned codes I suceeded to retrieve a cell value in a
lable control. But the problem is, I've no idea to display all the cells
value of that from where the cell in a row, a data was retrieved.

Any Idea!!

Please Help!! SOS Please consider!!

Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean
d = "A1: A5000"
Let c = txtbx1.Value

For Each Sh In ActiveWorkbook.Worksheets
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub

ElseIf Not b Is Nothing Then
firstAddress = b.Address
lbl1.Caption = b

Do
txtbx2.Value = c
Set b = .FindNext(b)
FoundIt = True
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With

Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub

What are you trying to display?

The addresses of all the found cells in all the worksheets in A1:A5000?

The values of some column on the same rows of all the found cells?

Since you're looping through all the worksheets and finding lots (maybe) on each
sheet, I'm confused.

Msgbox "Data not found" wrote:

Hi All,

I'm a baby to Excel Programming.

But I've to programme Excel 2003 work book in order that if sth is typed in
the text box control. The code searches all the worksheets and displays the
value in the label control.
I've a textbox control, label control and a command button control in Sheet 1

Now with the below mentioned codes I suceeded to retrieve a cell value in a
lable control. But the problem is, I've no idea to display all the cells
value of that from where the cell in a row, a data was retrieved.

Any Idea!!

Please Help!! SOS Please consider!!

Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean
d = "A1: A5000"
Let c = txtbx1.Value

For Each Sh In ActiveWorkbook.Worksheets
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub

ElseIf Not b Is Nothing Then
firstAddress = b.Address
lbl1.Caption = b

Do
txtbx2.Value = c
Set b = .FindNext(b)
FoundIt = True
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With

Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub

--

Dave Peterson

Dear Dave,

Appreciate for your response!!

For instance if my code detects a required data in a cell A1 of Sheet 2. so
I would like the workbook to be programmed to display the succeeding data in
the same row from A1 to H1. No matter if I have to place different label
controls for each column.

"Dave Peterson" wrote:

What are you trying to display?

The addresses of all the found cells in all the worksheets in A1:A5000?

The values of some column on the same rows of all the found cells?

Since you're looping through all the worksheets and finding lots (maybe) on each
sheet, I'm confused.

Msgbox "Data not found" wrote:

Hi All,

I'm a baby to Excel Programming.

But I've to programme Excel 2003 work book in order that if sth is typed in
the text box control. The code searches all the worksheets and displays the
value in the label control.
I've a textbox control, label control and a command button control in Sheet 1

Now with the below mentioned codes I suceeded to retrieve a cell value in a
lable control. But the problem is, I've no idea to display all the cells
value of that from where the cell in a row, a data was retrieved.

Any Idea!!

Please Help!! SOS Please consider!!

Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean
d = "A1: A5000"
Let c = txtbx1.Value

For Each Sh In ActiveWorkbook.Worksheets
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub

ElseIf Not b Is Nothing Then
firstAddress = b.Address
lbl1.Caption = b

Do
txtbx2.Value = c
Set b = .FindNext(b)
FoundIt = True
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With

Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub

--

Dave Peterson
.

Does this mean that you want exactly one item--the first match found displayed?

Or if you find a 32,325 matches on all 3,652 sheets, then you'd want all 32,325
matches (A:H) displayed?

(And why would you want the data from column A displayed--that's the field
you're matching on and it's shown in your label?)

Msgbox "Data not found" wrote:

Dear Dave,

Appreciate for your response!!

For instance if my code detects a required data in a cell A1 of Sheet 2. so
I would like the workbook to be programmed to display the succeeding data in
the same row from A1 to H1. No matter if I have to place different label
controls for each column.

"Dave Peterson" wrote:

What are you trying to display?

The addresses of all the found cells in all the worksheets in A1:A5000?

The values of some column on the same rows of all the found cells?

Since you're looping through all the worksheets and finding lots (maybe) on each
sheet, I'm confused.

Msgbox "Data not found" wrote:

Hi All,

I'm a baby to Excel Programming.

But I've to programme Excel 2003 work book in order that if sth is typed in
the text box control. The code searches all the worksheets and displays the
value in the label control.
I've a textbox control, label control and a command button control in Sheet 1

Now with the below mentioned codes I suceeded to retrieve a cell value in a
lable control. But the problem is, I've no idea to display all the cells
value of that from where the cell in a row, a data was retrieved.

Any Idea!!

Please Help!! SOS Please consider!!

Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean
d = "A1: A5000"
Let c = txtbx1.Value

For Each Sh In ActiveWorkbook.Worksheets
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub

ElseIf Not b Is Nothing Then
firstAddress = b.Address
lbl1.Caption = b

Do
txtbx2.Value = c
Set b = .FindNext(b)
FoundIt = True
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With

Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub

--

Dave Peterson
.


--

Dave Peterson

ps.

You may want to look at Jan Karel Pieterse's FlexFind:
http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp

It doesn't quite do all what you want, but it's very powerful.

Msgbox "Data not found" wrote:

Dear Dave,

Appreciate for your response!!

For instance if my code detects a required data in a cell A1 of Sheet 2. so
I would like the workbook to be programmed to display the succeeding data in
the same row from A1 to H1. No matter if I have to place different label
controls for each column.

"Dave Peterson" wrote:

What are you trying to display?

The addresses of all the found cells in all the worksheets in A1:A5000?

The values of some column on the same rows of all the found cells?

Since you're looping through all the worksheets and finding lots (maybe) on each
sheet, I'm confused.

Msgbox "Data not found" wrote:

Hi All,

I'm a baby to Excel Programming.

But I've to programme Excel 2003 work book in order that if sth is typed in
the text box control. The code searches all the worksheets and displays the
value in the label control.
I've a textbox control, label control and a command button control in Sheet 1

Now with the below mentioned codes I suceeded to retrieve a cell value in a
lable control. But the problem is, I've no idea to display all the cells
value of that from where the cell in a row, a data was retrieved.

Any Idea!!

Please Help!! SOS Please consider!!

Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean
d = "A1: A5000"
Let c = txtbx1.Value

For Each Sh In ActiveWorkbook.Worksheets
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub

ElseIf Not b Is Nothing Then
firstAddress = b.Address
lbl1.Caption = b

Do
txtbx2.Value = c
Set b = .FindNext(b)
FoundIt = True
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With

Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub

--

Dave Peterson
.


--

Dave Peterson

No, No, No, Whatever I have thought, may appear simple. for instance: My
search is concerned with three characters text or number. If my program finds
IND in sheet 3, my code displays IND only in the Label control at present. I
want the discriptions in the succeeding columns to be displayed in different
labels control. For example: A1 = IND, B1 = INDIA, C1 = ASIA, D1 = Largest
Democratic Country, E1 = Hindu etc.

Is it impossible?



"Dave Peterson" wrote:

ps.

You may want to look at Jan Karel Pieterse's FlexFind:
http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp

It doesn't quite do all what you want, but it's very powerful.

Msgbox "Data not found" wrote:

Dear Dave,

Appreciate for your response!!

For instance if my code detects a required data in a cell A1 of Sheet 2. so
I would like the workbook to be programmed to display the succeeding data in
the same row from A1 to H1. No matter if I have to place different label
controls for each column.

"Dave Peterson" wrote:

What are you trying to display?

The addresses of all the found cells in all the worksheets in A1:A5000?

The values of some column on the same rows of all the found cells?

Since you're looping through all the worksheets and finding lots (maybe) on each
sheet, I'm confused.

Msgbox "Data not found" wrote:

Hi All,

I'm a baby to Excel Programming.

But I've to programme Excel 2003 work book in order that if sth is typed in
the text box control. The code searches all the worksheets and displays the
value in the label control.
I've a textbox control, label control and a command button control in Sheet 1

Now with the below mentioned codes I suceeded to retrieve a cell value in a
lable control. But the problem is, I've no idea to display all the cells
value of that from where the cell in a row, a data was retrieved.

Any Idea!!

Please Help!! SOS Please consider!!

Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean
d = "A1: A5000"
Let c = txtbx1.Value

For Each Sh In ActiveWorkbook.Worksheets
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub

ElseIf Not b Is Nothing Then
firstAddress = b.Address
lbl1.Caption = b

Do
txtbx2.Value = c
Set b = .FindNext(b)
FoundIt = True
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With

Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub

--

Dave Peterson
.


--

Dave Peterson
.