1

I have a macro that searches and copies some date from one sheet back into another, if that value is found, and repeats for every row that's not blank.

In my current document, of around 150 rows, the following macro runs fine, but randomly throws up the error:

Run-time error '91': Object variable or With block variable not set

This is the macro:

Sub Update()

Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.StatusBar = "Cleaning New Roles..."

Sheets("new").Select
Range("A2").Select

Do Until IsEmpty(ActiveCell)

    Dim SearchValue As String

    SearchValue = ActiveCell.Value
    Sheets("Old").Select
    On Error GoTo Error_handler
    Cells.Find(What:=SearchValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False).Activate

    Range("R" & Selection.Row & ":T" & Selection.Row).Select
    Selection.Copy
    ActiveCell.Offset(0, -1).Select
    Sheets("new").Select
    Range("R" & Selection.Row).Select
    Selection.PasteSpecial Paste:=xlPasteValues

Error_handler:
    Sheets("new").Select
    Range("A" & Selection.Row).Select
    ActiveCell.Offset(1, 0).Select

Loop

Application.ScreenUpdating = True
Application.StatusBar = False

End Sub

The debug highlights the Cells.Find row.

I can't see why the first 100 rows are OK and then it breaks.

Any guidance would be very helpful.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Tim Wilkinson
  • 3,761
  • 11
  • 34
  • 62

3 Answers3

3

Try it without so much .Select.

Sub Update()
    Dim SearchValue As String, rFnd As Range, o As Long

    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Application.StatusBar = "Cleaning New Roles..."

    With Sheets("new")
        o = 0

        Do Until IsEmpty(Range("A2").Offset(o, 0))
            SearchValue = vbNullString
            SearchValue = .Range("A2").Offset(o, 0).Value

            With Sheets("Old")
                On Error GoTo Error_handler
                Set rFnd = .Cells.Find(What:=SearchValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)

                Sheets("new").Range("R2").Offset(o, 0).Resize(1, 3) = _
                  .Range("R" & rFnd.Row).Resize(1, 3).Value
            End With

Error_handler:
            o = o + 1
        Loop
    End With

    Application.ScreenUpdating = True
    Application.StatusBar = vbNullString

End Sub

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1
  • Jeeped, add checking for nothing in finding cell as shown in Bond's answer. If you do, you answer is more complete. – R.Katnaan Aug 19 '15 at 12:18
  • I didn't set up a full test environment for this but the error handler throwing to `Error_handler:` should take care of that and more closely resembles the OP's original code. –  Aug 19 '15 at 12:20
1

The Find() function returns a Range object if it's successful. You're assuming it always will be and you're chaining an Activate() call to the end:

Cells.Find(What:=SearchValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False).Activate

This will fail if the Find() fails. Instead, assign the return value to a Range object and test for success:

Dim r As Range
Set r = Cells.Find(What:=SearchValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)

If Not r Is Nothing Then
    ' Find successful
End If
Bond
  • 16,071
  • 6
  • 30
  • 53
0

Usually this error comes up if the searched string is not found. VBA doesn't come up with "Couldn't find what you are looking for" but throws this error message at you. So you need to make sure that everything you are feeding VBA to look for is actually there, or catch the error and resolve what to do if it isn't.

Blind Seer
  • 492
  • 1
  • 5
  • 17