find
 

Is there any way to program a workbook so that I am able to click on any
individual cell in a workbook, which contains multiple sheets, that would
then serach the entire workbook and automatically locate/find any other rows
in the entire workbook that contains information identical to that cell that
I originally selected?

find
 

Can't you use Excel's Find to do that? With the cell selected, press Ctrl+C
to copy the cell's value into the Clipboard, then press Edit/Find on Excel's
menu bar (or press Ctrl+F instead) to bring up the Find dialog box; press
Ctrl+V to paste the cell's value into the "Find what" field; then click the
Options button to expose all of the possible find options and select
Workbook in the "Within" drop-down; change the "Look in" drop-down to Values
and click the "Find All" button... you will get a list of all cells
containing the value you searched for (you can drag the bottom of the Find
dialog border to expose more of the list if you want).

Rick


"nhamilt" wrote in message
...
Is there any way to program a workbook so that I am able to click on any
individual cell in a workbook, which contains multiple sheets, that would
then serach the entire workbook and automatically locate/find any other
rows
in the entire workbook that contains information identical to that cell
that
I originally selected?

find
 

Thanks for your feedback. I am familar with Excel's find feature, however,
what I was hoping to do was program the workbook so that I could click on any
individual cell and it would automatically search for and bring up a list of
any other row(s) that has that same information. I am not sure if excel is
even capable of something like this--or if I need to record a macro. Either
way any help would be great.



"Rick Rothstein (MVP - VB)" wrote:

Can't you use Excel's Find to do that? With the cell selected, press Ctrl+C
to copy the cell's value into the Clipboard, then press Edit/Find on Excel's
menu bar (or press Ctrl+F instead) to bring up the Find dialog box; press
Ctrl+V to paste the cell's value into the "Find what" field; then click the
Options button to expose all of the possible find options and select
Workbook in the "Within" drop-down; change the "Look in" drop-down to Values
and click the "Find All" button... you will get a list of all cells
containing the value you searched for (you can drag the bottom of the Find
dialog border to expose more of the list if you want).

Rick


"nhamilt" wrote in message
...
Is there any way to program a workbook so that I am able to click on any
individual cell in a workbook, which contains multiple sheets, that would
then serach the entire workbook and automatically locate/find any other
rows
in the entire workbook that contains information identical to that cell
that
I originally selected?

find
 

How about something like the following then? Note that it is Workbook event
code, not Worksheet event code; to install the code correctly, go into the
VB editor and double click the ThisWorkbook entry in the Project Window and
then copy/paste the code following my signature into the code window that
appears. To make use of the code, just double click a cell that contains a
value you want to find.

Rick

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim WS As Worksheet
Dim CL As Range
Dim CellList As String
Dim FirstAddress As String
Cancel = True
If Target.Value = "" Then Exit Sub
For Each WS In Worksheets
Set CL = WS.UsedRange.Find(Target.Value, LookIn:=xlValues)
If Not CL Is Nothing Then
FirstAddress = CL.Address
Do
If Len(CellList) = 0 Then
CellList = WS.Name & " - " & CL.Address
Else
CellList = CellList & vbLf & WS.Name & " - " & CL.Address
End If
Set CL = WS.UsedRange.FindNext(CL)
Loop While Not CL Is Nothing And CL.Address < FirstAddress
End If
Next
MsgBox CellList
End Sub



"nhamilt" wrote in message
...

Thanks for your feedback. I am familar with Excel's find feature, however,
what I was hoping to do was program the workbook so that I could click on
any
individual cell and it would automatically search for and bring up a list
of
any other row(s) that has that same information. I am not sure if excel is
even capable of something like this--or if I need to record a macro.
Either
way any help would be great.



"Rick Rothstein (MVP - VB)" wrote:

Can't you use Excel's Find to do that? With the cell selected, press
Ctrl+C
to copy the cell's value into the Clipboard, then press Edit/Find on
Excel's
menu bar (or press Ctrl+F instead) to bring up the Find dialog box; press
Ctrl+V to paste the cell's value into the "Find what" field; then click
the
Options button to expose all of the possible find options and select
Workbook in the "Within" drop-down; change the "Look in" drop-down to
Values
and click the "Find All" button... you will get a list of all cells
containing the value you searched for (you can drag the bottom of the
Find
dialog border to expose more of the list if you want).

Rick


"nhamilt" wrote in message
...
Is there any way to program a workbook so that I am able to click on
any
individual cell in a workbook, which contains multiple sheets, that
would
then serach the entire workbook and automatically locate/find any other
rows
in the entire workbook that contains information identical to that cell
that
I originally selected?

find
 

Rick, that worked! Thanks.

"Rick Rothstein (MVP - VB)" wrote:

How about something like the following then? Note that it is Workbook event
code, not Worksheet event code; to install the code correctly, go into the
VB editor and double click the ThisWorkbook entry in the Project Window and
then copy/paste the code following my signature into the code window that
appears. To make use of the code, just double click a cell that contains a
value you want to find.

Rick

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim WS As Worksheet
Dim CL As Range
Dim CellList As String
Dim FirstAddress As String
Cancel = True
If Target.Value = "" Then Exit Sub
For Each WS In Worksheets
Set CL = WS.UsedRange.Find(Target.Value, LookIn:=xlValues)
If Not CL Is Nothing Then
FirstAddress = CL.Address
Do
If Len(CellList) = 0 Then
CellList = WS.Name & " - " & CL.Address
Else
CellList = CellList & vbLf & WS.Name & " - " & CL.Address
End If
Set CL = WS.UsedRange.FindNext(CL)
Loop While Not CL Is Nothing And CL.Address < FirstAddress
End If
Next
MsgBox CellList
End Sub



"nhamilt" wrote in message
...

Thanks for your feedback. I am familar with Excel's find feature, however,
what I was hoping to do was program the workbook so that I could click on
any
individual cell and it would automatically search for and bring up a list
of
any other row(s) that has that same information. I am not sure if excel is
even capable of something like this--or if I need to record a macro.
Either
way any help would be great.



"Rick Rothstein (MVP - VB)" wrote:

Can't you use Excel's Find to do that? With the cell selected, press
Ctrl+C
to copy the cell's value into the Clipboard, then press Edit/Find on
Excel's
menu bar (or press Ctrl+F instead) to bring up the Find dialog box; press
Ctrl+V to paste the cell's value into the "Find what" field; then click
the
Options button to expose all of the possible find options and select
Workbook in the "Within" drop-down; change the "Look in" drop-down to
Values
and click the "Find All" button... you will get a list of all cells
containing the value you searched for (you can drag the bottom of the
Find
dialog border to expose more of the list if you want).

Rick


"nhamilt" wrote in message
...
Is there any way to program a workbook so that I am able to click on
any
individual cell in a workbook, which contains multiple sheets, that
would
then serach the entire workbook and automatically locate/find any other
rows
in the entire workbook that contains information identical to that cell
that
I originally selected?