Display required succeeding cells value if a cell value A1 is capt

[Msgbox Data not found]

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

 

[Msgbox Data not found]

Hey!!

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

 

[Msgbox Data not found]

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?

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/showthread.php?t=181707

Microsoft Office Help

.

 

[Msgbox Data not found]

Hey,

What's the code if a search item repeats in the sheets? How to display them
in the summary sheet in a succeeding manner. I mean for instance:'Moscow'
available in two sheets , how to display them in summary sheet in a
consecutive manner. I used your code and modified according to my need. But
the problem, the code finds find more than one entry, the summary sheets
displays only the last one.

 

[Msgbox Data not found]

Hey!!

I've gone through your previous code and got the answer. I can get as many
sheets
record in the Summary sheet where the required data exist. So, no probs!!
But I need to clear the cells content in the Summary Sheet as I click on the
txtbx1.
I'm trying to use the For.........Next loop. Is it possible? How? My code
goes lengthy.
I need the clearance from 12th row in the summary sheet.

what do you mean by the last Item. do yo mean the last item in each
sheet or the last sheet. If it is the last sheet what determines the
sheet order?

I modified the code below to only put the last item in each sheet. I
eliminated the Do loop to search for multiple items on a sheet. I also
changed the Find method to search in reverse to get the last item on a
sheet.

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, _
SearchDirection:=xlPrevious)

If b Is Nothing Then
MsgBox "Data not found!!"
Else
Sh.Range("B" & c.Row & ":H" & c.Row).Copy _
Destination:=DestSht.Range("B" & NewRow)
DestSht.Range("A" & NewRow) = Sh.Name
NewRow = NewRow + 1
End If
End With
End If
Next
End Sub

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


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

Microsoft Office Help

.

 

[Msgbox Data not found]

Hi, Joel!!

The code worked well.

Now another query: Is it possible to display column headings in the 12th Row
of summary sheet along with the found data?
For instance: If the column heading is country and the searched data is USA.
The summary sheet should display the data USA in the A13 and the following
data in B13, C13 etc. row and Country above in A12 row.

Any idea!!

 

[Msgbox Data not found]

Hey, Joel,

Everything is fine till now!! A problem!! I want to distribute the Search
Code document, now the problem is that when I proctect the document so that
the user may not edit any portion in 'Summary' Sheet the code is not able to
delete the rows and an error occurs. But if the Summary sheet is unprotected
it works fine.
So how to proctect the sheet and let a user search the required data.
And another query, a user needs to set the security level low to run the
Seach Code Workbook. Isn't it possible to let the user use the Wkbk with
playing with the security level? Please help!!

 

[Msgbox Data not found]

thanks Joel for all the help! Your help made me understand the macro work.
Still I need the sytax description!! I'll catch you again for help
Thanks again!!

 

[Msgbox Data not found]

Hey!! Joel!!

Help again!!

I've pasted a column of data in a sheet of the Search Code Workbook. The
Macro is unable to detect a three letters code. I found that the code has
unwanted space at the end. I used a TRIM() function but of no use. The Macro
is unable to detect.
How can we get rid of the unwanted space in the worksheet in the same column
where it is pasted? Please help?
And one thing my email account doesn't recieve the replies notification from
this discussion page. Every time I have to go to this site and manually
search the threade by inserting a page number to find my thread. How come? I
do check at Notify me of replies!! Please help!!

thanks Joel for all the help! Your help made me understand the macro work.
Still I need the sytax description!! I'll catch you again for help
Thanks again!!

The security level and the Sheet/workbook protection are two different
properties and not related. The Security level allow macros to run.
The protection allows the worksheet to be changed. You don't need to
have macros in a workbook to incorporate the protection property.


You need to have your macro unprotect the workbook/worksheet before you
delete the rows iin the Summary sheet. You can have or not have a
password associated with the protection property. If you do havve a
password it will be visible to the users in the macro unless you protect
the macro code with a password and make the macros invisible. Making
VBA code hidden to the users make it impossible for users users to find
and fix bugs that may exist in the macros. So you have to make some
tradeoffs in determining what properties you use in the macro and
workbook.

If you trust the users then you don't need to protect the macro with a
password.


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

Excel Live Chat

.