CODEWITHSUNDEEP
vbaexcelexcel-2010
SASUSMC
SASUSMC

Reputation: 681

Object Variable Error in Code

I am receiving an object variable error when I run a Macro.

Object Variable or with block variable not set

This occurs on the second time I run the Macro through. Here is the code that is causing the error:

' Select the first Junk Row and use it to delete all rows afterward
     With ActiveSheet
        LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
    End With

    With Columns("F")
        .Find(What:="Total", After:=.Cells(1, 1), LookIn:=xlValues).Activate - **Error occurs here.**
    End With
    A = ActiveCell.Row
    Range(A & ":" & LastRow).Delete

Any suggestions would be greatly appreciated. If I shut the program down it works fine the next time through.

Thanks, Scott

Upvotes: 0

Views: 49

Answers (3)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

Try and replace the 

.find (...).activate

by:

.find (...).select

Even if i disagree with the use of select/activate.

simplest was 

A= .find (....) .row  
'might return 0 or <nothing> or error , if no match ( .match is faster by the way)

Upvotes: 0

Sathish Kothandam
Sathish Kothandam

Reputation: 1520

Before activating the cell . its good to check whether the .find found the cell or not .. Below is the example .

Tested

Sub testing()
Dim rng As Range
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
    End With

    With Columns("F")
      Set rng = .Find(What:="Total", After:=.Cells(1, 1), LookIn:=xlValues) 
    End With

    If Not rng Is Nothing Then

    A = rng.Row

    Range(A & ":" & LastRow).Delete

    Else

    MsgBox ("Total not found")

    End If

End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

The first time you run the code it deletes the bottom rows including the row containing "Total". The second time you run the code it fails because the first pass deleted the "Total"

Upvotes: 1

Related Questions