CODEWITHSUNDEEP
excelvba
Évariste Galois
Évariste Galois

Reputation: 1033

VBA "Application defined or object defined error"

I have the following line of VBA code which is intended to find a column by header name and get the entire column as a range:

Set w1 = wbk.Sheets("Sheet1")
Set parentRange = w1.Rows(1).Find("portfolioName").Offset(1).Resize(num_rows - 1, 1)

It works perfectly fine in one workbook, however when I try it in a new workbook I get the following error:

<Application-defined or object-defined error>

I'm new to VBA so I'm not sure whether or not displaying code context or giving more details, is there something simple I could be neglecting to notice?

num_rows:

num_rows = w1.Cells(Rows.Count, 1).End(xlUp).row

Upvotes: 1

Views: 750

Answers (2)

Tim Williams
Tim Williams

Reputation: 166331

Dim f1 As Range, f2 As Range

Set w1 = wbk.Sheets("Sheet1")
'find first instance
Set f1 = w1.Rows(1).Find("portfolioName",lookat:=xlWhole)
If Not f1 Is Nothing then
    'find second instance
    Set f2 = f1.offset(0, 1).Resize(1, w1.Columns.Count - f1.Column).Find("portfolioName",lookat:=xlWhole)
    If not f2 is nothing then
        'set range based on f2
        Set parentRange = w1.Range(f2.Offset(1, 0), _
                                   w1.cells(rows.count,f2.column).end(xlup))

    end if
end if

In your posted code you get the number of rows from ColA, but it seems like that might be unreliable? In the code above it just selects all data below the second instance of the search term.

Upvotes: 2

Justin
Justin

Reputation: 65

It's either not finding portfolioName (if you have 'option compare text' off you might be running into case-sensitivity issues), or num_rows is not getting defined properly. Give those two a check.

Upvotes: 1

Related Questions