VBA Getting Runtime Error 91, but not every time?

Question

When I skip through the sub where it shows the problem, this happens:

I can't seem to resolve the Issue since i use the code snippet from this sub multiple times and it works fine.. Code here


Private Sub UserForm_Initialize()

Sheets("Data").Range("A:T").AutoFilter Field:=18
Sheets("Data").Range("A:T").AutoFilter Field:=18, Criteria1:="FALSCH"
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "I:I"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Dim lRow As Long
Dim lCol As Long

    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

This error is because you currently try to assign a value to `lRow` without checking if your `Cells.Find` actually returns an `Range` object. In case nothing is found, you are trying to assing the `Row` property of thin air to your variable, hence the error. `If not is Nothing Then` is a lifesaver here, but you'll need to assign `Cells.Find` to a `Range` variable first through `Set`. — JvdV, Jun 03 '20 at 10:40
Another great tip is to fully qualify your ranges (be *explicit*). Set `Workbook` and `Worksheet` variables as shown [here](https://stackoverflow.com/q/10714251/9758194) to avoid stuff like `Cells.Find` and `Range("A1")` which are referencing the then active worksheet, also known as *implicit* sheet references. — JvdV, Jun 03 '20 at 10:44
@JvdV so like this? ```Dim ws As Worksheet Set ws = Worksheets("Data") lRow = ws.Cells.Find(What:="*" ``` — Leon S, Jun 03 '20 at 10:51
No you'll need an actual `Range` variable which you `Set` when you perform [`Range.Find`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find). The `Workbook` and `Worksheet` variables are there for being *explicit* about what workbook/sheet your code refers to. — JvdV, Jun 03 '20 at 10:53
THanks, i got it now. I searched up [This](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) thread and used another way to count to the last row, which only uses 3 lines of code, way easier to understand and also works for me. — Leon S, Jun 03 '20 at 10:58

score 4 · Accepted Answer · answered Jun 03 '20 at 10:57

The structure should be something like this:

Dim ws As Worksheet
Set ws = Worksheets("Data")

Dim FoundAt As Range
Set FoundAt = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False)

If Not FoundAt Is Nothing Then
    Dim lRow As Long
    lRow = FoundAt.Row

    'your other code here …
Else
    MsgBox "Find failed!"
End If

First try to find the cell with ws.Cells.Find and reference it to a range variable FoundAt so you can test if a cell was found or not using If Not FoundAt Is Nothing Then, because .Row only exists if a cell was found. If you found nothing then nothing has no row (obviously).

VBA Getting Runtime Error 91, but not every time?

1 Answers1