Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |