.Find problem

crugg1 · January 22nd, 2009, 10:17 AM

Can anyone help me with why I'm getting an "Object variable or With block variable not set (Error 91)" error on the highlighted code below?? Its probably something simple that I'm missing ... just cant see it ... Thanks!!

Code:

 
Sub test1()
'test1 macro
Dim Data As Range
Dim cRange, pRange, R, Oppty As Range
Dim cLastRow, pLastRow, x, OpptyCol As Integer
'Set range for Current data
Set cRange = Worksheets("current").Range("A1").specialcells(xlCellTypeLastCell)
'Set range for previous data
Set pRange = Worksheets("previous").Range("A1").specialcells(xlCellTypeLastCell)
'Find last rows
cLastRow = cRange.Row
pLastRow = pRange.Row
    
'Find Forecasted column
Set R = Worksheets("previous").Range("A1:AZ1").Find(What:="Forecasted?", LookAt:=xlPart)
'Find Oppty # column
Set Oppty = Worksheets("previous").Range("A1:AZ1").Find(What:="Oppty #", LookAt:=xlPart)
'Set column #
OpptyCol = Oppty.Column
For x = 2 To pLastRow
'look in current worksheet in range a1:a1000
With Worksheets("current").Range("a1:a1000")
'for each cell in the Oppty # column - previous worksheet - to return the value in the 5th column
    vName = .Find(What:=Cells(x, OpptyCol), after:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 5)
End With
'enter value in following cell
    R.Offset((x - 1), 1) = vName
Next x
End Sub

Shasur · January 23rd, 2009, 02:30 AM

It might be because of absence of required text and Find doesn't return anything

You can check that by :

If Not R Is Nothing Then
Dim vName As Range
For x = 2 To pLastRow
'look in current worksheet in range a1:a1000
With Worksheets("current").Range("a1:a1000")
'for each cell in the Oppty # column - previous worksheet - to return the value in the 5th column
Set vName = .Find(What:=Cells(x, OpptyCol), After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not vName Is Nothing Then
vName = vName.Offset(0, 5)
End If
End With
'enter value in following cell
If Not vName Is Nothing Then
R.Offset((x - 1), 1) = vName
End If
Next x
End If

Cheers
Shasur