1

I'm working in excel on a database that uses values from one sheet to update another. There is a sheet named "Line List" which contains line numbers (unique identifiers for piping) and lists information for each line such as size material etc. The other sheet, "Database" then contains a number of different tables for different instruments that can be placed on the lines such as valves and pressure regulators. I was creating two buttons on a form, one of which updates all of the instrument tables on the "Database" sheet with new line data based on the cell the user is selecting. The other button updates all tables with all of the lines. They both utilize class modules and a class function called FindMatchingRows. It originally worked, but is now giving the error "Run time Error '91' Object variable or with block variable not set" and debug brings me to this Line "matchingRows = oLine.FindMatchingRows(TableName, LineNum)" I will show the code bellow and would love if anyone could shed some light on the issue.

Private Sub editTable(TableName As String, LineNum As String, oLine As LineClass)
    Dim oInstrument As Variant
    Dim ws As Worksheet
    Dim tbl As Object
    Dim i As Integer
    i = 1
    Dim matchingRows As Variant
    Set ws = Nothing
    Set tbl = Nothing
    
    Set ws = ThisWorkbook.Sheets("Line List")
    Set tbl = ws.ListObjects("LineTable")

    matchingRows = oLine.FindMatchingRows(TableName, LineNum)
    
    If VarType(matchingRows) = 3 Then
        Exit Sub
    End If


    wsName = "Database"
    Select Case TableName
        Case "LCVTable"
            Set oInstrument = New LCVClass
        Case "VCVTable"
            Set oInstrument = New VCVClass
        Case "OOVTable"
            Set oInstrument = New OOVClass
        Case "FITable"
            Set oInstrument = New FIClass
        Case "PRTable"
            Set oInstrument = New PRClass
        Case "MITable"
            Set oInstrument = New MIClass
        Case "TITable"
            Set oInstrument = New TIClass
        Case "LITable"
            Set oInstrument = New LIClass
        Case "PITable"
            Set oInstrument = New PIClass
        Case Else
            MsgBox "Unexpected Instrument selected"
    End Select
    
    For Each Item In matchingRows
        Dim temp As Integer
        temp = matchingRows(i)
        oInstrument.EditInstrument oLine, temp
        i = i + 1
    Next Item
    
    Set ws = Nothing
    Set tbl = Nothing
    
End Sub
Function FindMatchingRows(TableName As String, searchString As String) As Variant
    
    Dim tbl As ListObject
    Dim ws As Worksheet
    Dim rng As Range
    Dim foundRows() As Variant
    Dim i As Long
    Dim rowCount As Long
    Dim matchCount As Long
    
    Set ws = Nothing
    Set tbl = Nothing
    ' Initialize worksheet and table
    Set ws = ThisWorkbook.Sheets("Database")  ' Change "Sheet1" to your sheet name
    Set tbl = ws.ListObjects(TableName)

    If tbl.ListRows.count > 0 Then
        lastRow = tbl.Range.Rows.count
    End If
            
    ' Define the range to search in (column 5 of the table)
    Set rng = tbl.ListColumns(5).DataBodyRange
    
    rowCount = rng.Rows.count
    matchCount = 0

    
    ' Loop through each cell in the range
    For i = 1 To rowCount
        If rng.Cells(i, 1).value = searchString Then
            ' Resize array to store the matching row number
            ReDim Preserve foundRows(1 To matchCount + 1)
            foundRows(matchCount + 1) = i
            matchCount = matchCount + 1
        End If
    Next i
    If matchCount = 0 Then
        'MsgBox "No matching lines in " & tableName
        FindMatchingRows = matchCount
        Exit Function
    End If
    ' Return the array of matching row numbers
    FindMatchingRows = foundRows
    
    Set ws = Nothing
    Set tbl = Nothing
    
End Function

I've messed around a bunch thinking that maybe oLine was previously set as something else and closed and reopened excel, and I'm kinda at a loss on what to do.

3
  • 1
    Exactly how are you calling editTable ? Commented Jul 9 at 20:50
  • ...most likely you're not passing an object to oLine Commented Jul 9 at 20:55
  • If you put a breakpoint on the line where you get the error and press F8 when you reach that line, do you get the error immediatly or you are able to step inside the FindMatchingRows function? Commented Jul 9 at 23:17

0

Browse other questions tagged or ask your own question.