find

[nhamilt]

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?

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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]

Rick, that worked! Thanks.

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