1

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
Évariste Galois
  • 1,043
  • 2
  • 13
  • 27
  • Does this new workbook have a sheet named `"Sheet1"` ? It could also be that `.Find()` is just not returning anything and so the range `parentRange` doesn't end up with a value – Marcucciboy2 Aug 23 '18 at 19:01
  • ...and what's the value of `num_rows` when it fails? – Tim Williams Aug 23 '18 at 19:02
  • 1
    `Range.Find` will return `Nothing` if it doesn't find what it's looking for, which will throw error 91 since you can't do `Nothing.Offset`. Also `Range.Find` "remembers" its optional parameter values between calls, so manually doing Ctrl+F and tweaking stuff between macro calls will make the macro behave differently - you should provide a value for each of the optional parameters. – Mathieu Guindon Aug 23 '18 at 19:04
  • @TimWilliams it seems like num_rows is 1, which I believe is what is causing the Range to be Nothing as Mathieu suggested. I have no idea why this is the case, I updated the post with how num_rows is calculated and it works in another sheet. – Évariste Galois Aug 23 '18 at 19:21
  • Sometimes chaining calls is the cause of problems. To find the root of the problem piece of code, split chaining calls into separate calls. For instance, `Set parentRange = w1.Rows(1).Find("portfolioName").Offset(1).Resize(num_rows - 1, 1)`. First, `w1.Rows(1)` can't be incorrect since there's will be always first row. Next, `Find(...)` - this one returns `Range`. Assign the return value into its own variable. Next step (if `Find` worked perfectly), you fetch `Offset(1)` and so far and so on... – JohnyL Aug 23 '18 at 19:32
  • You need to add a check on the value of `num_rows` to decide what to do if it's 1 – Tim Williams Aug 23 '18 at 19:33
  • Ah, the issue is that there are two columns with the same name in the sheet, and I need the second one which does have actual rows under it. How can I specify this in the find command? – Évariste Galois Aug 23 '18 at 19:38
  • you migth be pulling the wrong `Rows`. Try instead `num_rows = w1.Cells(w1.Rows.Count, 1).End(xlUp).row` – John Alexiou Aug 24 '18 at 14:41
  • See [Making a CountRows function in Excel](https://stackoverflow.com/a/45405235/380384) for a robust way of counting non-empty rows under a cell reference. – John Alexiou Aug 24 '18 at 14:43

2 Answers2

2
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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    Would you mind explaining what this code does? Why is the 2nd param of resize hardcoded to 200/what does it mean? – Évariste Galois Aug 23 '18 at 19:52
  • To find `f2` you want to look to the right of `f1` (starting from the cell to the right of `f1`) - I just hardcoded 200 as a maximum number of columns you might ever need to check when doing that, but if you want to check the whole of the rest of the row then `Resize(1, ws.Columns.Count - f1.Column)` would do that. – Tim Williams Aug 23 '18 at 20:00
  • It gives me another run-time error, saying Object Required on the Set parentRange line. Am I missing something? – Évariste Galois Aug 23 '18 at 20:09
  • Sorry that line should have had `f2.Column` – Tim Williams Aug 23 '18 at 20:10
1

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.

Justin
  • 65
  • 5