Continue to receive the run-time error below in VB Code

Question

I keep getting the error shown below on line 32 of my code. Essentially all I'm trying to do is to copy the Project and Client values from one table in a worksheet to another table in a worksheet. I can get the code to work to simply copy only the project, but I'd like for it to copy both if possible.

Any ideas to why this keeps happening?

Error:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define worksheets and tables
    Dim wsEngagements As Worksheet
    Dim wsPnL As Worksheet
    Dim tblEngagement As ListObject
    Dim tblFinance As ListObject
    
    ' Set worksheet references
    Set wsEngagements = ThisWorkbook.Sheets("Engagements")
    Set wsPnL = ThisWorkbook.Sheets("P&L")
    
    ' Set table references
    Set tblEngagement = wsEngagements.ListObjects("Engagement")
    Set tblFinance = wsPnL.ListObjects("Finance")
    
    ' Check if the change occurred in the Project column of the Engagement table
    If Not Intersect(Target, tblEngagement.ListColumns("Project").DataBodyRange) Is Nothing Then
        ' Get the changed project name
        Dim projectName As String
        projectName = Target.Value
        
        ' Get the corresponding client name
        Dim clientName As String
        Dim clientColumnIndex As Integer
        clientColumnIndex = tblEngagement.ListColumns("Client").Index
        
        ' Find the row in the Engagement table where the project name is entered
        Dim engagementRow As ListRow
        Set engagementRow = tblEngagement.ListRows(Target.Row - tblEngagement.HeaderRowRange.Row)
        
        ' Get the client name from the same row in the Client column
        clientName = engagementRow.ListObjects("Engagement").ListColumns("Client").DataBodyRange.Value
        
        ' Check if the project name is not empty
        If projectName <> "" Then
            ' Check if the project name already exists in the Finance table
            If WorksheetFunction.CountIf(tblFinance.ListColumns("Project").DataBodyRange, projectName) = 0 Then
                ' Find the first empty row in the Finance table
                Dim financeRow As ListRow
                Set financeRow = tblFinance.ListRows.Add
                
                ' Copy the project name and client name to the Finance table
                financeRow.ListObject.ListColumns("Project").DataBodyRange.Value = projectName
                financeRow.ListObject.ListColumns("Client").DataBodyRange.Value = clientName
                
                ' Inform the user about the successful copy
                MsgBox "Project name '" & projectName & "' and client '" & clientName & "' copied to Finance table."
            Else
                ' Inform the user if the project name already exists in the Finance table
                MsgBox "Project name '" & projectName & "' already exists in Finance table."
            End If
        End If
    End If
End Sub

Answer 1

engagementRow is a ListRow object. It doesn't have ListObjects property.

    ' Get the client name from the same row in the Client column
    clientName = engagementRow.ListObjects("Engagement").ListColumns("Client").DataBodyRange.Value

Revised code lines are marked with '**'.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define worksheets and tables
    Dim wsEngagements As Worksheet
    Dim wsPnL As Worksheet
    Dim tblEngagement As ListObject
    Dim tblFinance As ListObject
    ' Set worksheet references
    Set wsEngagements = ThisWorkbook.Sheets("Engagements")
    Set wsPnL = ThisWorkbook.Sheets("P&L")
    ' Set table references
    Set tblEngagement = wsEngagements.ListObjects("Engagement")
    Set tblFinance = wsPnL.ListObjects("Finance")
    ' Check if the change occurred in the Project column of the Engagement table
    If Not Intersect(Target, tblEngagement.ListColumns("Project").DataBodyRange) Is Nothing Then
        ' Get the changed project name
        Dim projectName As String
        projectName = Target.Value
        ' Get the corresponding client name
        Dim clientName As String
        Dim clientColumnIndex As Integer
        clientColumnIndex = tblEngagement.ListColumns("Client").Index
        ' Find the row in the Engagement table where the project name is entered
        Dim engagementRow As ListRow
        Set engagementRow = tblEngagement.ListRows(Target.Row - tblEngagement.HeaderRowRange.Row)
        ' Get the client name from the same row in the Client column
        clientName = engagementRow.Range(clientColumnIndex).Value   '**'
        ' Check if the project name is not empty
        If projectName <> "" Then
            ' Check if the project name already exists in the Finance table
            If WorksheetFunction.CountIf(tblFinance.ListColumns("Project").DataBodyRange, projectName) = 0 Then
                ' Find the first empty row in the Finance table
                Dim financeRow As ListRow, projectColIndex As Long, clientColIndex As Long   '**'
                Set financeRow = tblFinance.ListRows.Add
                clientColIndex = tblFinance.ListColumns("Client").Index   '**'
                projectColIndex = tblFinance.ListColumns("Project").Index   '**'
                ' Copy the project name and client name to the Finance table
                financeRow.Range(projectColIndex).Value = projectName   '**'
                financeRow.Range(clientColIndex).Value = clientName   '**'
                ' Inform the user about the successful copy
                MsgBox "Project name '" & projectName & "' and client '" & clientName & "' copied to Finance table."
            Else
                ' Inform the user if the project name already exists in the Finance table
                MsgBox "Project name '" & projectName & "' already exists in Finance table."
            End If
        End If
    End If
End Sub

Microsoft documentation:

ListRow.Range property (Excel)

ListColumn.Index property (Excel)