Object Variable Error in Code

Question

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

Not sure why it didn't I manually went through and saw there was a match. Have since instituted a workaround by using another column however I would like to know why this is occuring. Thanks. — SASUSMC
– SASUSMC, Commented Feb 20, 2014 at 0:33
Tim is right. also .Find argument LookAt is by default set to xlPart so if there really is Total in there, even if you have trailing or leading spaces, it will find it. — L42
– L42, Commented Feb 20, 2014 at 1:29

Gary's Student · Accepted Answer · 2014-02-20 02:21:19Z

1

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"

answered Feb 20, 2014 at 2:21

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sathish Kothandam · Accepted Answer · 2014-02-20 03:32:55Z

0

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

answered Feb 20, 2014 at 3:32

Sathish Kothandam

1,5203 gold badges16 silver badges35 bronze badges

Comments

Patrick Lepelletier · Accepted Answer · 2014-02-20 14:40:22Z

0

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)

answered Feb 20, 2014 at 14:40

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

Collectives™ on Stack Overflow

Object Variable Error in Code

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related