Solved: Find is not matching expected values

[scott56]

Hi,

I have been trying to get .Find to work for the following sample without success. I have a list of values in a workbook that need to be searched for in another workbook and if found anywhere in the source workbook set the cell next to the searched for value to TRUE.

I think I have the syntax correct....any help or advice appreciated...

Sub FindAndMatchValues()
'This routine will attempt to find and match the values from the Value sheet
Dim myRange As Range
Dim mySourceRange As Range
Dim myResult As Range
Dim i As Integer
Dim mySourceWorkbook As Workbook
Dim objExcelObject As Object
'Open the source workbook and then set the range for the values to be searched through
Set objExcelObject = CreateObject("Excel.Application")
objExcelObject.Visible = False
Set mySourceWorkbook = objExcelObject.Workbooks.Open("Data Find Source.xls")
Set mySourceRange = mySourceWorkbook.Worksheets("Sheet1").Range("A1")
Set mySourceRange = mySourceRange.Offset(0, 1)
Set mySourceRange = Range(mySourceRange.Address, mySourceRange.End(xlDown).Address)

Set myRange = Worksheets("Values").Range("ValuesStartHeading")
i = 1
Do While myRange.Offset(i, 0).Value <> ""

With mySourceRange
Set myResult = .Find(Trim(myRange.Offset(i, 0).Value), LookIn:=xlValues)
If Not myResult Is Nothing Then
myRange.Offset(i, 1).Value = True
Else
myRange.Offset(i, 1).Value = False
End If
End With

i = i + 1
Loop
If Not (objExcelObject Is Nothing) Then
objExcelObject.Workbooks("Data Find Source.xls").Close SaveChanges:=False
objExcelObject.Quit
Set objExcelObject = Nothing
End If
End Sub

[p45cal]

This is your problem line:
Set mySourceRange = Range(mySourceRange.Address, mySourceRange.End(xlDown).Address)
change to: Set mySourceRange = mySourceRange.Parent.Range(mySourceRange.Address, mySourceRange.End(xlDown).Address)
Normally, the likes of:Set mySourceRange = Range(mySourceRange, mySourceRange.End(xlDown)) would work but it didn't, perhaps because it's in another instance of Excel. Which begs the question Why are you opening the second file in another instance of Excel? Is it to be able to hide it? If so you could set ScreenUpdating to False while the operation is going on and open the second file in the same instance of Excel. Eg this works:
Sub FindAndMatchValues()
'This routine will attempt to find and match the values from the Value sheet
Application.ScreenUpdating = False
Dim myRange As Range
Dim mySourceRange As Range
Dim myResult As Range
Dim i As Integer
Dim mySourceWorkbook As Workbook
'Open the source workbook and then set the range for the values to be searched through
Set mySourceWorkbook = Workbooks.Open("Data Find Source.xls")
Set mySourceRange = mySourceWorkbook.Worksheets("Sheet1").Range("A1")
Set mySourceRange = mySourceRange.Offset(0, 1)
Set mySourceRange = Range(mySourceRange, mySourceRange.End(xlDown))
Set myRange = ThisWorkbook.Worksheets("Values").Range("ValuesStartHeading")
i = 1
Do While myRange.Offset(i, 0).Value <> ""
With mySourceRange
Set myResult = .Find(Trim(myRange.Offset(i, 0).Value), LookIn:=xlValues)
If Not myResult Is Nothing Then
myRange.Offset(i, 1).Value = True
Else
myRange.Offset(i, 1).Value = False
End If
End With
i = i + 1
Loop
Workbooks("Data Find Source.xls").Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

btw, these two lines are hard coded:
Set mySourceRange = mySourceWorkbook.Worksheets("Sheet1").Range("A1")
Set mySourceRange = mySourceRange.Offset(0, 1)
so why not condense them to:
Set mySourceRange = mySourceWorkbook.Worksheets("Sheet1").Range("B1")
and since you've created mySourceWorkbook, use it to close the workbook too, saves specifying the file name again:mySourceWorkbook.Close SaveChanges:=False

[scott56]

Thanks for your suggestions I will try them all out and post back my results....the 2nd instance of Excel was to open another workbook from an existing workbook.....If I can do this and hide the other workbook then it acheives the same aim....so I will try this.

[scott56]

I tried each of the suggestions and they all worked for the problem I was trying to solve...

Thanks for your help...