Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search acrross Workbboks & Worksheets???
This pseudo code should get you started:
Dim lbox as Listbox set lbox = Sheet1!Listbox1 for each wkbk in Workbooks for each ws in wkbk.Worksheets With ws.cells Set c = .Find("Target", lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do lbox.AddItem c.Address(0,0,xlA1,True) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next ws Next wkbk -- Regards, Tom Ogilvy "Chris" wrote in message ... Hi I hope someone can help me out there. I need to SEARCH for a string across all open workbooks & all sheets with in those workbook. Then list the location of each find in a List Box. Once user select a location from List it takes them to it. Is it possible ???? I use XL2002 on Win2K Thanks a million in advance Regards Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search acrross Workbboks & Worksheets???
Hi Tom
Thank you so much for the help. I almost got it working now. Please see below. I have used it on Userform2. It works fine & [populate the List Box with all find within all open workbooks & worksheet & it also is fine when selecting any range with in active workbook, however it fails & gives run time error "Acticate methood failed" when I try to select something which is not the active book. Please advise what changes are required to ListBox1_Click procedure to make it work Thanks a million Private Sub ListBox1_Click() With Me.ListBox1 Range(ListBox1.Value).Activate End With End Sub Private Sub UserForm_Initialize() For Each wkbk In Workbooks For Each WS In wkbk.Worksheets With WS.Cells Set c = .Find("289917-002", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Me.ListBox1.AddItem c.Address(0, 0, xlA1, True) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next WS Next wkbk End Sub -----Original Message----- This pseudo code should get you started: Dim lbox as Listbox set lbox = Sheet1!Listbox1 for each wkbk in Workbooks for each ws in wkbk.Worksheets With ws.cells Set c = .Find("Target", lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do lbox.AddItem c.Address(0,0,xlA1,True) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next ws Next wkbk -- Regards, Tom Ogilvy "Chris" wrote in message ... Hi I hope someone can help me out there. I need to SEARCH for a string across all open workbooks & all sheets with in those workbook. Then list the location of each find in a List Box. Once user select a location from List it takes them to it. Is it possible ???? I use XL2002 on Win2K Thanks a million in advance Regards Chris . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search acrross Workbboks & Worksheets???
Private Sub ListBox1_Click()
Application.Goto range(Listbox1.Value) , True End Sub if that doesn't work Private Sub ListBox1_Click() Dim rng as Range set rng = Range(Listbox1.Value) rng.parent.parent.Activate rng.parent.Activate rng.select End Sub -- Regards, Tom Ogilvy "Chris" wrote in message ... Hi Tom Thank you so much for the help. I almost got it working now. Please see below. I have used it on Userform2. It works fine & [populate the List Box with all find within all open workbooks & worksheet & it also is fine when selecting any range with in active workbook, however it fails & gives run time error "Acticate methood failed" when I try to select something which is not the active book. Please advise what changes are required to ListBox1_Click procedure to make it work Thanks a million Private Sub ListBox1_Click() With Me.ListBox1 Range(ListBox1.Value).Activate End With End Sub Private Sub UserForm_Initialize() For Each wkbk In Workbooks For Each WS In wkbk.Worksheets With WS.Cells Set c = .Find("289917-002", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Me.ListBox1.AddItem c.Address(0, 0, xlA1, True) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next WS Next wkbk End Sub -----Original Message----- This pseudo code should get you started: Dim lbox as Listbox set lbox = Sheet1!Listbox1 for each wkbk in Workbooks for each ws in wkbk.Worksheets With ws.cells Set c = .Find("Target", lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do lbox.AddItem c.Address(0,0,xlA1,True) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next ws Next wkbk -- Regards, Tom Ogilvy "Chris" wrote in message ... Hi I hope someone can help me out there. I need to SEARCH for a string across all open workbooks & all sheets with in those workbook. Then list the location of each find in a List Box. Once user select a location from List it takes them to it. Is it possible ???? I use XL2002 on Win2K Thanks a million in advance Regards Chris . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search acrross Workbboks & Worksheets???
Wow!!!!
The 1st option worked right away..thanks a million -----Original Message----- Private Sub ListBox1_Click() Application.Goto range(Listbox1.Value) , True End Sub if that doesn't work Private Sub ListBox1_Click() Dim rng as Range set rng = Range(Listbox1.Value) rng.parent.parent.Activate rng.parent.Activate rng.select End Sub -- Regards, Tom Ogilvy "Chris" wrote in message ... Hi Tom Thank you so much for the help. I almost got it working now. Please see below. I have used it on Userform2. It works fine & [populate the List Box with all find within all open workbooks & worksheet & it also is fine when selecting any range with in active workbook, however it fails & gives run time error "Acticate methood failed" when I try to select something which is not the active book. Please advise what changes are required to ListBox1_Click procedure to make it work Thanks a million Private Sub ListBox1_Click() With Me.ListBox1 Range(ListBox1.Value).Activate End With End Sub Private Sub UserForm_Initialize() For Each wkbk In Workbooks For Each WS In wkbk.Worksheets With WS.Cells Set c = .Find("289917-002", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Me.ListBox1.AddItem c.Address(0, 0, xlA1, True) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next WS Next wkbk End Sub -----Original Message----- This pseudo code should get you started: Dim lbox as Listbox set lbox = Sheet1!Listbox1 for each wkbk in Workbooks for each ws in wkbk.Worksheets With ws.cells Set c = .Find("Target", lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do lbox.AddItem c.Address(0,0,xlA1,True) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next ws Next wkbk -- Regards, Tom Ogilvy "Chris" wrote in message news:08bc01c3adee$4db07ee0 ... Hi I hope someone can help me out there. I need to SEARCH for a string across all open workbooks & all sheets with in those workbook. Then list the location of each find in a List Box. Once user select a location from List it takes them to it. Is it possible ???? I use XL2002 on Win2K Thanks a million in advance Regards Chris . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for a particular cell in worksheets | Excel Worksheet Functions | |||
Search for duplicates across three worksheets | Excel Worksheet Functions | |||
Search in Multiple Worksheets | Excel Discussion (Misc queries) | |||
search worksheets | Excel Discussion (Misc queries) | |||
Search Mutiple WorkSheets | Excel Programming |