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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

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/showthread.php?t=181707
> 
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> 
> .
> 

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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

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/showthread.php?t=181707
> 
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> 
> .
> 

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

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

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/showthread.php?t=181707
> 
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> 
> .
> 

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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

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/showthread.php?t=181707
> 
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> 
> .
> 

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

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

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/showthread.php?t=181707
> 
> [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]
> 
> .
> 

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

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

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/showthread.php?t=181707
> 
> [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]
> 
> .
> 

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

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

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/showthread.php?t=181707
> 
> [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]
> 
> .
> 

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/showthread.php?t=181707
> > 
> > [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]
> > 
> > .
> > 

I stopped using the Microsoft forums because I wasn't getting the email
notifications.  Instead I moved to 'The Code Cage - Microsoft Office
Help - Microsoft Office Discussion' (http://www.THECODECAGE.com) to get
mail notifications.  A lot of people instead get the VBN notification by
registering for the postings.

Here is some test code to help get the ascii characters in the cell. 
Simply change the sheet names and cell address to one of the cells that
appearr to hae an extra character.  the extra character may be another
invisible white character like a tab that is giving you the problem.
Once you know the character you can use the replace statementt to remove
the extra characters.


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

http://www.thecodecage.com/forumz/chat.php