CODEWITHSUNDEEP
vbaexcelfor-loopfind
IIJHFII
IIJHFII

Reputation: 600

vba range.find method stops on random cells

I wrote this quick For Loop as part of my macro to find cells in utwbk, based on a list of values in dewbk. The loop seems to go fine for a few cells and then crashes. The problem is, it crashes for different values each time but the values DO exist in utwbk. Here is my code:

Dim utpath As String
Dim utwbk As Workbook
Dim ogfund As String
Dim ogcell As Range
Dim newfund As String
Dim newcell As Range
Dim t As Long


For t = 2 To tempfundlastrow
If dewbk.Sheets("Macro").Cells(t, 1).Value <> "" Then
    Set ogcell = dewbk.Sheets("Macro").Cells(t, 1)
    ogfund = Trim(ogcell.Value)

    With utwbk.Sheets("Report").Range(Cells(1, 1), Range("AAA1").SpecialCells(xlCellTypeLastCell))
    Set newcell = .Find(ogfund, LookIn:=xlValues, lookat:=xlWhole)
    End With

    newfund = newcell.Value

    newcell.Offset(2, 0).Value = ogcell.Offset(0, 8).Value
    newcell.Offset(3, 0).Value = ogcell.Offset(0, 9).Value
    newcell.Offset(4, 0).Value = ogcell.Offset(0, 11).Value
    newcell.Offset(6, 0).Value = ogcell.Offset(0, 10).Value
Else
    'nothing
End If
Next t

The code crashes with run-time error 91: 'Object variable of with block variable not set' on this line:

newfund = newcell.Value

In the previous line where I define newcell, ogfund has a value and I can find that value in utwbk so really not sure what's going on. I am assuming my syntax for the .Find is incorrect but I do not know how to rectify this. As usual, any help is greatly appreciated!

Upvotes: 1

Views: 329

Answers (2)

Slai
Slai

Reputation: 22876

It's a common mistake. In 

utwbk.Sheets("Report").Range(Cells(1, 1), Range("AAA1").SpecialCells(xlCellTypeLastCell))

Cells(1, 1) and Range("AAA1") refer to ranges in the currently active sheet

What you probably want is more like 

With utwbk.Sheets("Report")
    With .Range(.Cells(1, 1), .Range("AAA1").SpecialCells(xlCellTypeLastCell))
        Set newcell = .Find(ogfund, LookIn:=xlValues, lookat:=xlWhole)
    End With
End With

or just 

With utwbk.Sheets("Report").UsedRange
    Set newcell = .Find(ogfund, LookIn:=xlValues, lookat:=xlWhole)
End With

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149315

After the line

Set newcell = .Find(ogfund, LookIn:=xlValues, lookat:=xlWhole)

Type this

If newcell Is Nothing Then
    MsgBox "Not found"
    Exit Sub
End If

If you see the message box that means .Find couldn't find the search text and since it couldn't find, the newcell.Value will break the code as newcell is Nothing

BTW if you look for a word BLAH and in your cell you have BLAH with leading and trailing spaces then your .Find will not find the word because you are using lookat:=xlWhole. Maybe you want lookat:=xlPart

Upvotes: 3

Related Questions