VBA find function getting error 424 object required [duplicate]

Question

I have code getting error 424 object required

lr = Range("O:O").Cells(Rows.Count, 1).End(xlUp).Row

For y = 0 To UBound(myVariable)
    a = myVariable(y)
    Range("O:O").Select
    Set objXL = GetObject(, "Excel.Application")
    Set z = Cells.Find(What:=a, After:=Range("O2"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    If z = "True" Then
        ActiveCell.Delete shift:=xlUp
    End If
    MsgBox z.Value
Next

A good read siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba — Siddharth Rout
– Siddharth Rout, Commented Nov 23, 2012 at 9:44

CaBieberach · Accepted Answer · 2020-10-20 08:25:58Z

1

Find retrieves an range Object. So you either:

a) Activate the found range

Cells.Find(What:=a, After:=Range("O2"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

OR

b) Assign the found range to a variable

Set z = Cells.Find(What:=a, After:=Range("O2"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)      'No .Activate in here '

Using both at the same time will produce an error.

NOTE:

Be carefull. If .Find does NOT find a match, it will retrieve Nothing. In such a case the .Activate will pop an error msg. So use some error handling in here.

edited Oct 20, 2020 at 8:25

answered Nov 23, 2012 at 9:13

CaBieberach

1,7682 gold badges17 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

VBA find function getting error 424 object required [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related