How do I fix a "Subscript out of range" error for VBA Find method?

Question

The first part of my code is supposed to find the last active row in a worksheet - my dataset is comprised of formulas that may return blanks, so I do want to use the "Find" method. The worksheet "WS1 Bid Data" exists, but I get a "Subscript out of range" error when I try to run the code.

I know there are tons of questions similar to this, but I have not been able to find a working solution. Does anyone have ideas?

    start_row = Worksheets("Import").Range("B23").Value
    start_col = Worksheets("Import").Range("B24").Value
    start_ref = Worksheets("Import").Range("B19").Value
    With Worksheets("WS1 Bid Data")
        end_row = Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      LookAt:=x1Part, _
                      LookIn:=x1Formulas, _
                      SearchOrder:=x1ByRows, _
                      SearchDirection:=x1Previous, _
                      MatchCase:=False).Row
    End With

By the way, if you want to actually reference the `With Worksheets("WS1 Bid Data")`, you need a period in front of `Cells`: `.Cells.Find...` — BigBen, Jul 29 '20 at 19:04
You may want to include a Workbook reference as well, in case it's looking in the wrong one. — braX, Jul 29 '20 at 19:04
That's interesting your Excel constants are using 1 in place of L: `x1Part` vs `xlPart`. — Parfait, Jul 29 '20 at 19:25

Parfait · Accepted Answer · 2020-07-29T19:47:55.140

As comments indicate, consider a few best practices in Excel VBA:

Use Option Explicit in every sheet or module running VBA. This will raise a compile error for variables not declared and hence should catch the misspelling of 1 in Excel constants (i.e., x1Part vs. xlPart).

In fact, set this rule as a global option right now in VBA IDE under: Tools > Options > Editor > check 'Require Variable Declaration'.
Always explicitly qualify objects to their parents. Do not ever rely on where cursor or screen is activated which adjusts workbook or worksheet contexts and affects code. And always avoid Select or .Activate.

Specifically, use ThisWorkbook. (or assigned workbook via Set wb = ...) on your Worksheet objects.
Fully use period qualifiers within a With...End block. Again, this avoids misconstruing the context your code runs within as your Cells reference without period assumes the active worksheet. And this may not necessarily be same as the worksheet of With block.

Doing the above, your code should be more stable and maintainable:

Option Explicit

Public Sub myMacro()
    ...
    With ThisWorkbook.Worksheets("Import")                    ' USE ThisWorkbook.
       start_row = .Range("B23").Value
       start_col = .Range("B24").Value
       start_ref = .Range("B19").Value
    End With

    With ThisWorkbook.Worksheets("WS1 Bid Data")              ' USE ThisWorkbook.
        end_row = .Cells.Find(What:="*", _                    ' USE .Cells
                              After:=.Range("A1"), _
                              LookAt:=xlPart, _               ' VERIFY WITH Option Explicit
                              LookIn:=xlFormulas, _           ' SAME AS ABOVE
                              SearchOrder:=xlByRows, _        ' SAME AS ABOVE
                              SearchDirection:=xlPrevious, _  ' SAME AS ABOVE
                              MatchCase:=False).Row
    End With
    ...
End Sub

`x1` strikes yet again... sheesh. – BigBen Jul 29 '20 at 19:46 — BigBen, Jul 29 '20 at 19:46

How do I fix a "Subscript out of range" error for VBA Find method?

1 Answers1