Solved: .Find not working between Workbooks

[CatDaddy]

error in highlighted section...RT error 91 object variable or with block not set...the same formula works between two sheets of the same workbook...but not two separate workbooks?!?!?!


[VBA]For i = 3 To lRow
Set findStr = Workbooks(wb).Sheets(1).Range("C" & i)
Workbooks(wbA).Sheets(1).Activate

Range("B1:B2000").Find _
(What:=findStr, _
LookIn:=xlValues, _
LookAT:=xlPart, _
SearchOrder:=xlByRows).Activate

foundCell = ActiveCell.Offset(0, -1).Value
Workbooks(wb).Worksheets(1).Cells(i, 9).Value = foundCell
Next i[/VBA]

[p45cal]

This code wouldn't be in a sheet's code module would it (a sheet which isn't Workbooks(wbA).Sheets(1))?

Untested, try:[vba]With Workbooks(wb).Sheets(1)
For i = 3 To lRow
Set findStr = .Range("C" & i)
Set c = Workbooks(wbA).Sheets(1).Range("B1:B2000").Find _
(What:=findStr, _
LookIn:=xlValues, _
LookAT:=xlPart, _
SearchOrder:=xlByRows)
If Not c Is Nothing Then .Cells(i, 9).Value = c.Offset(0, -1).Value
Next i
End With
[/vba]

[CatDaddy]

i used Match instead...i didnt need the value just the location...

[VBA]For i = 3 To lRow
Set findStr = Workbooks(wb).Worksheets(1).Range("C" & i)

Workbooks(wbA).Worksheets(1).Activate

On Error Resume Next
sRow = Application.Match(findStr, Range("B:B"), 0)
If Err.Number <> 0 Then
'no match found
Err.Clear
Else
foundCell = Cells(sRow, 1).Value
Workbooks(wb).Worksheets(1).Activate
Cells(i, 9).Value = foundCell
End If
Next i[/VBA]

[CatDaddy]

turns out the problem was the woman i work with gave me the wrong source sheet...the error was just because the values didnt exist in the second workbook (only 60000 lines )