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

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

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

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

Display required succeeding cells value if a cell value A1 is
 

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

.

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

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

Display required succeeding cells value if a cell value A1 is
 

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

.

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

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

Display required succeeding cells value if a cell value A1 is
 

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

.

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

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

Display required succeeding cells value if a cell value A1 is
 

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

.

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

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

Display required succeeding cells value if a cell value A1 is
 

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

.

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

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

Display required succeeding cells value if a cell value A1 is
 

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

.

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

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

Display required succeeding cells value if a cell value A1 is
 

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

.

Display required succeeding cells value if a cell value A1 is
 

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

.

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

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

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