scott56
08-14-2009, 02:24 PM
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
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