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.
editTable
?oLine
FindMatchingRows
function?