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

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 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
>
> .
>

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.

"joel" wrote:

>
> 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
>
> .
>

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.

"joel" wrote:

>
> 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/sh...d.php?t=181707
>
> Microsoft Office Help
>
> .
>

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!!

"joel" wrote:

>
> There are three different things you can do
>
>
> 1) Delete the row
>
> Rows(12).delete
>
> or all rows after row 12
>
> LastRow = rows.count
> Rows("12:" & LastRow).delete
>
>
> 2) clear the cell and formating
>
> Rows(12).clear
>
> or all rows after row 12
>
> LastRow = rows.count
> Rows("12:" & LastRow).clear
>
> 3) clear the cells and not the formating
>
> Rows(12).clearcontents
>
> or all rows after row 12
>
> LastRow = rows.count
> Rows("12:" & LastRow).clearcontents
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181707
>
> Excel Live Chat
>
> .
>

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!!



"joel" wrote:

>
> if we go back to the Find statement
>
>
> Set b = .Find(c, LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows)
> You can get the row as follows:
>
> MyRow = b.row
> MyCol = b.column
>
>
> To get the an item in the same column in row 12 would be something like
> this
>
> MyHeader = Cells(12,Mycol)
>
>
> We put the Sheet name in column A so to put the header in column B
> would be
>
> Range("B" & Newrow) = MyHeader
>
>
> The you would have to move the rest of the row from column b to C. so
> you would need to make the following change
>
> from:
> Sh.Range("B" & c.Row & ":H" &
> c.Row).Copy _
> Destination:=DestSht.Range("B" & NewRow)
> DestSht.Range("A" & NewRow) = Sh.Name
> NewRow = NewRow + 1
>
> To:
> Sh.Range("B" & c.Row & ":H" &
> c.Row).Copy _
> Destination:=DestSht.Range("C" & NewRow)
> DestSht.Range("A" & NewRow) = Sh.Name
> NewRow = NewRow + 1
>
> You can address any cell in the worksheet two ways
>
> 1) Use Range which has a letter column and row number
>
> Range("A1")
>
> The Range contains a string in double quotes so you can combine two
> Strings
>
> Myrow = 25
> Range("A" & Myrow)
>
>
> 2) You can use Cells which contains a column number instead of the
> letter
>
> Cells(1,25)
>
> When using the Find method you get a column number instead of a letter
> so you need to use Cells rather than Range.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181707
>
> Excel Live Chat
>
> .
>

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!!



"joel" wrote:

>
> 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/sh...d.php?t=181707
>
> Excel Live Chat
>
> .
>

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!!


"Msgbox "Data not found"" wrote:

> 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!!
>
>
>
> "joel" wrote:
>
> >
> > 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/sh...d.php?t=181707
> >
> > Excel Live Chat
> >
> > .
> >