Searching in another workbook for a string and returning with the contents of the cell next to it.

[Hendy88]

Hello all,

I'm trying to create a loop macro that will take in the contents of the
cells in Column A (starting at row 2), then search for those same
contents in two other workbooks, returning the cell to the left of what
it finds in those two other workbooks to Column B in the starting
workbook. If that made any sense.

So starting in Cell A2 (in Workbook1) I want to take the contents of
that cell and search for it in another workbook (that's already open).
Once it finds (or if it even finds) what it's looking for (let's say in
Column M) in the second workbook (Workbook2), it takes the contents of
the cell immediately to the left of the result cell (in this case Cell
L2), and returns it to B2 of Wookbook1. If it didn't find the results
in Workbook2, then I need it search in Workbook3 (and do the same
thing... return what's in the cell to the left).

I have a start on it, but didn't continue changing it to what I need it
to do because when it runs I get "Object variable or With block
variable not set".


Sub ReturnValue()

Dim CellContents As String
Dim Results As Range
Dim i As Integer

i = 2
For i = 2 To 3000 '3000 really needs to be the end of the range in
Column A

CellContents = Range("A" & i).Value

Windows("Workbook2.xls").Activate
Set Results = Cells.Find(What:=CellContents, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate

If Results = null Then

Windows("Workbook3.xls").Activate
Set Results = Cells.Find(What:=CellContents, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate

Windows("Workbook1").Activate

Range("B" & i).Value = Results

Next i

End Sub


Again this is just the basis, and I didn't even make it to the part of
determining what cell was to the left of the results it found. And
ultimately, if it doesn't find anything in either Workbook2 or
Workbook3, I need it to just leave a blank.

I hope someone understands what I'm trying to do and can help me out.
The reason the for loop is set to 3000 is because, that's about how
many I'd have to do manually.

Thanks in advance!

 

[Bernie Deitrick]

Hendy,

If you know the names of the two sheets as well, you can use something like this, run when the
Workbook1 with the list in column A is the active sheet (Written for the data being found on
Sheet1). I'm not sure if you really want to use xlPart or xlWhole as your search criteria.....

Sub ReturnValue2()

Dim CellContents As String
Dim Results As Range
Dim i As Long

For i = 2 To Range("A65536").End(xlUp).Row 'To the end of the range in Column A

CellContents = Range("A" & i).Value

Set Results = Workbooks("WorkBook2.xls").Worksheets("Sheet1").Cells.Find( _
What:=CellContents, _
LookIn:=xlFormulas, _
LookAt:=xlPart)

If Results Is Nothing Then
Set Results = Workbooks("WorkBook3.xls").Worksheets("Sheet1").Cells.Find( _
What:=CellContents, _
LookIn:=xlFormulas, _
LookAt:=xlPart)
End If

If Not Results Is Nothing Then
Range("B" & i).Value = Results.Offset(0, -1).Value
Else
Range("B" & i).Value = "Not Found"
End If

Next i

End Sub

HTH,
Bernie
MS Excel MVP