I don't use the match excel function much from vba, but I don't think it
returns a range, but instead the position within an array (ex. if the value
was found in the 5th row of the lookup array J2:J9, it would return a 5).

If you want the range, I'm confident something like this would work:

Replace the line:
res = Application.Match(ActiveCell, rng2, 0)

With this:
With rng2
Dim rngCell As Range
Set rngCell = .Find( _
What:=ActiveCell, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
SearchFormat:=False _
)
End With

Then replace:
ActiveCell.Offset(0, 9).Activate

With this:
rngCell.Offset(0, 9).Select


Of course, you will have to add/change your error checking.



"GregR" wrote:

I am close with a few minor details. Here is what I have so far:
Sub CheckProjInTwo()
Dim rng1 As Range
Dim rng2 As Range
Dim rng As Range
Dim wkbk As Workbook
Dim wkbk1 As Workbook
Dim wkbk2 As Workbook
Set wkbk = ActiveWorkbook
Set wkbk1 = Workbooks("RF 340-000.xls")

With wkbk.Worksheets(1)
Set rng1 = .Range(.Cells(7, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
With wkbk1.Worksheets(1)
Set rng2 = .Range(.Cells(7, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

With wkbk.Worksheets(1)
res = Application.Match(ActiveCell, rng2, 0)

If Not IsError(res) Then
wkbk1.Activate
ActiveCell.Offset(0, 9).Activate '<<<<<<activeCell here expected to
be the "res" address in Rng2
Else
MsgBox "Project not in WIP"
End If
End With

End Sub
I expected the "res" to be the active cell, but it isn't. If someone
can help with that problem and add a loop for all projects wkbk.rng1,
I'll have it. TIA

Greg