Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in a 2nd workbook
I need to search for a value found in one workbook on a second workbook.
Unfortunately, the .Find function seems to find the value in the first workbook (valuePointLicenses(0).XLS) rather than the one where I want it to search (SDI_Sales_Alignments_ 022708.xls) Thanks for the help Option Explicit Dim Temp As Long Dim TempName As Range Dim LastRow As Long Sub Copy_Alignments() Application.Windows("valuePointLicenses(0).XLS").A ctivate MsgBox ActiveWorkbook.Name Cells(2, 1).Select LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Temp = ActiveCell.Value Application.Windows("SDI_Sales_Alignments_ 022708.xls").Activate MsgBox ActiveWorkbook.Name With ActiveSheet MsgBox ActiveCell.Value Set TempName = Cells.Find(What:=Temp, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) End With If TempName Is Nothing Then MsgBox "Not Found" Else MsgBox TempName.Address End If Windows("valuePointLicenses(0).XLS").Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in a 2nd workbook
I tested this in Excel 2007 and it worked OK. However, in lieu of:-
With ActiveSheet Try using:- With ActiveWorkbook.ActiveSheet -- Regards, OssieMac "Rafi" wrote: I need to search for a value found in one workbook on a second workbook. Unfortunately, the .Find function seems to find the value in the first workbook (valuePointLicenses(0).XLS) rather than the one where I want it to search (SDI_Sales_Alignments_ 022708.xls) Thanks for the help Option Explicit Dim Temp As Long Dim TempName As Range Dim LastRow As Long Sub Copy_Alignments() Application.Windows("valuePointLicenses(0).XLS").A ctivate MsgBox ActiveWorkbook.Name Cells(2, 1).Select LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Temp = ActiveCell.Value Application.Windows("SDI_Sales_Alignments_ 022708.xls").Activate MsgBox ActiveWorkbook.Name With ActiveSheet MsgBox ActiveCell.Value Set TempName = Cells.Find(What:=Temp, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) End With If TempName Is Nothing Then MsgBox "Not Found" Else MsgBox TempName.Address End If Windows("valuePointLicenses(0).XLS").Activate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in a 2nd workbook
OssieMac - Thanks for the quick reply.
Any other thoughts? I have changed With ActiveSheet to With ActiveWorkbook.ActiveSheet and it still results in a found value on the original workbook rather than the one which is supposed to be active - SDI_Sales_Alignments_ 022708.xls Thanks "OssieMac" wrote: I tested this in Excel 2007 and it worked OK. However, in lieu of:- With ActiveSheet Try using:- With ActiveWorkbook.ActiveSheet -- Regards, OssieMac "Rafi" wrote: I need to search for a value found in one workbook on a second workbook. Unfortunately, the .Find function seems to find the value in the first workbook (valuePointLicenses(0).XLS) rather than the one where I want it to search (SDI_Sales_Alignments_ 022708.xls) Thanks for the help Option Explicit Dim Temp As Long Dim TempName As Range Dim LastRow As Long Sub Copy_Alignments() Application.Windows("valuePointLicenses(0).XLS").A ctivate MsgBox ActiveWorkbook.Name Cells(2, 1).Select LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Temp = ActiveCell.Value Application.Windows("SDI_Sales_Alignments_ 022708.xls").Activate MsgBox ActiveWorkbook.Name With ActiveSheet MsgBox ActiveCell.Value Set TempName = Cells.Find(What:=Temp, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) End With If TempName Is Nothing Then MsgBox "Not Found" Else MsgBox TempName.Address End If Windows("valuePointLicenses(0).XLS").Activate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find cell in Excel2000 workbook with link from another workbook? | Excel Discussion (Misc queries) | |||
Open a specific workbook...find value from other open workbook and then insert cells values in cell next to it. | Excel Programming | |||
How can I find value from another workbook | Excel Worksheet Functions | |||
Find within Workbook. | Excel Discussion (Misc queries) | |||
Cant find workbook | Excel Programming |