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.eg:A1. But the problem is, I've no idea to display all the
cells
value(B1 to H1) of that row 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

I appears yo umay be looking for multiple occurances of the data.
Create a new worksheet called Summary and then use the code below.

Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean

DestSht = sheets("Summary")
NewRow = 1


d = "A1: A5000"
Let c = txtbx1.Value

If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub


For Each Sh In ActiveWorkbook.Worksheets
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not b Is Nothing Then
firstAddress = b.Address
FoundIt = True

Do
sh.Range("B" & c.row & ":H" & c.row).copy
destination:=DestSht.range("B" & NewRow)
DestSht.Range("A" & Newrow) = sh.name
Newrow = Newrow + 1

Set b = .FindNext(after:=b)
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181707

Microsoft Office Help

Hey!!

run time error: 438
Object doesn't support this property or method and highlights the "Summary"
code line. What is it?


"joel" wrote:


I appears yo umay be looking for multiple occurances of the data.
Create a new worksheet called Summary and then use the code below.

Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean

DestSht = sheets("Summary")
NewRow = 1


d = "A1: A5000"
Let c = txtbx1.Value

If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub


For Each Sh In ActiveWorkbook.Worksheets
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not b Is Nothing Then
firstAddress = b.Address
FoundIt = True

Do
sh.Range("B" & c.row & ":H" & c.row).copy
destination:=DestSht.range("B" & NewRow)
DestSht.Range("A" & Newrow) = sh.name
Newrow = Newrow + 1

Set b = .FindNext(after:=b)
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181707

Microsoft Office Help

.

I left Set out of the statement. found a few other problems. this
should work.

from
DestSht = sheets("Summary")

to
Set DestSht = sheets("Summary")


Make sure you add a sheet Summary manually.



VBA Code:
--------------------


Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean

Set DestSht = Sheets("Summary")
NewRow = 1


d = "A1: A5000"
Let c = txtbx1.Value

If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub
End If

For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name < "Summary" Then
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not b Is Nothing Then
firstAddress = b.Address
FoundIt = True

Do
Sh.Range("B" & c.Row & ":H" & c.Row).Copy _
Destination:=DestSht.Range("B" & NewRow)
DestSht.Range("A" & NewRow) = Sh.Name
NewRow = NewRow + 1

Set b = .FindNext(after:=b)
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With
End If
Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub


--------------------


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181707

Microsoft Office Help

I inserted a new worksheet named "Summary"
But
I got a run time error '424'
Object required.
Highlighted in yellow the below mentioned code line
Sh.Range("B" & c.Row & ":H" & c.Row).Copy Destination:=DestSht.Range("B" &
NewRow)
Especially, about this new modified code: What it will do?


"joel" wrote:


I left Set out of the statement. found a few other problems. this
should work.

from
DestSht = sheets("Summary")

to
Set DestSht = sheets("Summary")


Make sure you add a sheet Summary manually.




VBA Code:
--------------------



Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean

Set DestSht = Sheets("Summary")
NewRow = 1


d = "A1: A5000"
Let c = txtbx1.Value

If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub
End If

For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name < "Summary" Then
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not b Is Nothing Then
firstAddress = b.Address
FoundIt = True

Do
Sh.Range("B" & c.Row & ":H" & c.Row).Copy _
Destination:=DestSht.Range("B" & NewRow)
DestSht.Range("A" & NewRow) = Sh.Name
NewRow = NewRow + 1

Set b = .FindNext(after:=b)
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With
End If
Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub



--------------------


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181707

Microsoft Office Help

.

I usually use the variabble c when using the find method since the VBA
help code uses the variable c. You used the variable B instead of c.

from
Sh.Range("B" & c.Row & ":H" & c.Row).Copy _
Destination:=DestSht.Range("B" & NewRow)

to

Sh.Range("B" & b.Row & ":H" & b.Row).Copy _
Destination:=DestSht.Range("B" & NewRow)


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181707

Microsoft Office Help