PDA

View Full Version : Solved: .Find not working between Workbooks



CatDaddy
07-25-2011, 03:17 PM
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?!?!?!


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

p45cal
07-25-2011, 03:45 PM
This code wouldn't be in a sheet's code module would it (a sheet which isn't Workbooks(wbA).Sheets(1))?

Untested, try: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

CatDaddy
07-25-2011, 04:20 PM
i used Match instead...i didnt need the value just the location...

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

CatDaddy
07-25-2011, 04:21 PM
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 :()