VBA Error - Run-time error '91': Object variable or With block variable not set

Question

I am getting error code 91 but can't determine what I need to fix.  Everything looks good but I am clearly not right.  This code populates a list box that will populate controls to add training when a line is double ckicked in the box.

Any ideas as to what I can do to fix this?

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'declare the variables

    Dim ID As String

    Dim I As Integer

    Dim findvalue As Range

'get the select value from the listbox

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

'set the listbox column

            ID = lstLookup.List(I, 14)

        End If

    Next I

'find the value in the range

 Set findvalue = Sheet2.Range("H:H").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)

'add the values to the userform controls

    cNum = 10

    For X = 1 To cNum

    Me.Controls("Reg" & X).Value = findvalue

        Set findvalue = findvalue.Offset(0, 1)

    Next

'disable the controls to make the user select an option

    With Me

    .cmdAdd.Enabled = False

    .cmdAdd.BackColor = RGB(225, 225, 225)

    .cmdEdit.Enabled = False

    .cmdEdit.BackColor = RGB(225, 225, 225)

    .cmdTraining.Enabled = False

    .cmdTraining.BackColor = RGB(225, 225, 225)

    .optAdd = False

    .optEdit = False

    .optTraining = False

    End With

End Sub

Answer 1

Sorry, I should have read closer.  I added the additional code "<<<" and get "Compile Error: Next without For":

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

On Error GoTo errHandler

'declare the variables

    Dim ID As String

    Dim I As Integer

    Dim cNum As Long, X As Long

    Dim findvalue As Range

     'error block

    On Error GoTo errHandler:

'get the select value from the listbox

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

'set the listbox column

        ID = lstLookup.List(I, 14)

        If lstLookup.Selected(I) = False Then

        Exit For

        End If

    Next I

  If VBA.Len(ID) < 1 Then '<<<<

        MsgBox "No data found"

        Cancel = True '<<<        Exit Sub '<<<<

    End If

'find the value in the range

 Set findvalue = Sheet2.Range("H:H").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)

'add the values to the userform controls

  cNum = 8

    For X = 1 To cNum

    Me.Controls("Reg" & X).Value = findvalue

        Set findvalue = findvalue.Offset(0, 1)

    Next

'disable the controls to make the user select an option

    With Me

    .cmdAdd.Enabled = False

    .cmdAdd.BackColor = RGB(225, 225, 225)

    .cmdEdit.Enabled = False

    .cmdEdit.BackColor = RGB(225, 225, 225)

    .cmdTraining.Enabled = False

    .cmdTraining.BackColor = RGB(225, 225, 225)

    .optAdd = False

    .optEdit = False

    .optTraining = False

    End With

'error block

    On Error GoTo 0

    Exit Sub '<<<<

errHandler::

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please notify the administrator"

End Sub

Answer 2

Re:  going in circles

The error message 'Next without For' can also appear when you have ' If without End If '.

All of the changes I made were not incorporated.

Each " If " requires an " End If  "

Your last post shows...

'---

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

       'set the listbox column

        ID = lstLookup.List(I, 14)

        If lstLookup.Selected(I) = False Then

        Exit For

        End If

    Next I

  **** If VBA.Len(ID) < 1 Then

        MsgBox "No data found"

        Cancel = True
        Exit Sub

    End If

'---

It should be...

'---

    For I = 0 To 1stLookup.ListCount - 1
        If 1stLookup.Selected(I) = True Then
         'set the listbox column
          ID = 1stLookup.List(I, 14)
          Exit For
        End If
    Next I
    If VBA.Len(ID) < 1 Then
        MsgBox "No data found"
        Cancel = True
        Exit Sub
    End If

'---

Also, dots are important.
To connect the range(s) to "Sheet2", dots are required.

With Sheet2

   .Range("B8:R30000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _

   .Range("HistData!Criteria"), CopyToRange:=.Range("AD8:AT30000"), Unique:= False

End With

Without the dots "Range" refers to the active sheet not Sheet2.

'---

NLtL

Answer 3

Re:  going in circles

The error message 'Next without For' can also appear when you have ' If without End If '.

All of the changes I made were not incorporated.

Each " If " requires an " End If  "

Your last post shows...

'---

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

       'set the listbox column

        ID = lstLookup.List(I, 14)

        If lstLookup.Selected(I) = False Then

        Exit For

        End If

    Next I

  **** If VBA.Len(ID) < 1 Then

        MsgBox "No data found"

        Cancel = True
        Exit Sub

    End If

'---

It should be...

'---

    For I = 0 To 1stLookup.ListCount - 1
        If 1stLookup.Selected(I) = True Then
         'set the listbox column
          ID = 1stLookup.List(I, 14)
          Exit For
        End If
    Next I
    If VBA.Len(ID) < 1 Then
        MsgBox "No data found"
        Cancel = True
        Exit Sub
    End If

'---

Also, dots are important.
To connect the range(s) to "Sheet2", dots are required.

With Sheet2

   .Range("B8:R30000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _

   .Range("HistData!Criteria"), CopyToRange:=.Range("AD8:AT30000"), Unique:= False

End With

Without the dots "Range" refers to the active sheet not Sheet2.

'---

NLtL

I changed the code as requested and it blew up.  I saw that the code in your "should be" changed the lstLookup to 1stLookup.  Changing that worked but threw another code back to the original issue I was having (see below). 

Answer 4

Re:  going in circles

The error message 'Next without For' can also appear when you have ' If without End If '.

All of the changes I made were not incorporated.

Each " If " requires an " End If  "

Your last post shows...

'---

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

       'set the listbox column

        ID = lstLookup.List(I, 14)

        If lstLookup.Selected(I) = False Then

        Exit For

        End If

    Next I

  **** If VBA.Len(ID) < 1 Then

        MsgBox "No data found"

        Cancel = True
        Exit Sub

    End If

'---

It should be...

'---

    For I = 0 To 1stLookup.ListCount - 1
        If 1stLookup.Selected(I) = True Then
         'set the listbox column
          ID = 1stLookup.List(I, 14)
          Exit For
        End If
    Next I
    If VBA.Len(ID) < 1 Then
        MsgBox "No data found"
        Cancel = True
        Exit Sub
    End If

'---

Also, dots are important.
To connect the range(s) to "Sheet2", dots are required.

With Sheet2

   .Range("B8:R30000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _

   .Range("HistData!Criteria"), CopyToRange:=.Range("AD8:AT30000"), Unique:= False

End With

Without the dots "Range" refers to the active sheet not Sheet2.

'---

NLtL

Thanks for the .Range.  I totally overlooked that piece of code.  This is frustrating for me so I can only imagine how it is for someone that understands more than I do.  I appreciate all your help.  This code works very well in another project I have and am using in the original form. It is nearly identicle to this one and most of the code is the same with the addition of a few more boxes. I do not get any of the errors in that project that I am getting with this project.

Answer 5

re:  frustrating

I've gone about as far as I can.
Suggest you check the value of ID and determine if it is in column(H).

Good luck with it.

NLtL