How to avoid error #NA when executing my macro-CodePudding

I have this error with my macro. My macro takes data from a table and in another sheet, outputs in a table my data for each value of a third sheet.

So let's say my table's value are : Jack and Daniel. And on my third sheet, I have Football and Rugby. The output in the second page will be :

Jack Football
Jack Rugby
Daniel Football
Daniel Rugby

Here is my macro :

Sub yo()
Dim Letters, Chk, Ele As Range, i As Long: Letters = Sheets("Sports").Range("C3:C5").Value
For Each Ele In Sheets("Students").ListObjects(1).ListColumns(1).DataBodyRange
    With Sheets("OK").ListObjects(1)
        Chk = Application.Match(Ele, .ListColumns(1).Range, 0)
        If IsError(Chk) Then
            For i = 1 To 3
                .ListRows.Add.Range = Array(Ele, Letters(i, 1))
            Next i
        End If
    End With
Next Ele
End Sub

However this works fine. The problem comes from all the other columns of the table in my second sheet. They all get the value "#NA". So instead of having nothing or formulas expanding down, there is that error.

How can I overcome this error ?

CodePudding user response：

Copy to Excel Table (`ListObject`)

The short answer is that in this case a ListRow has four columns yet you're assigning it an array of only two. By the looks of your answer, you have concluded this yourself (.Resize(, 2)).

An Improvement

Option Explicit

Sub AddStudents()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim wsSports As Worksheet: Set wsSports = wb.Sheets("Sports")
    Dim Sports(): Sports = wsSports.Range("C3:C5").Value
    Dim SportsCount As Long: SportsCount = UBound(Sports, 1)
    
    Dim wsStudents As Worksheet: Set wsStudents = wb.Sheets("Students")
    Dim loStudents As ListObject: Set loStudents = wsStudents.ListObjects(1)
    Dim lcStudents As ListColumn: Set lcStudents = loStudents.ListColumns(1)
    Dim rgStudents As Range: Set rgStudents = lcStudents.DataBodyRange
    
    Dim wsOK As Worksheet: Set wsOK = wb.Sheets("OK")
    Dim loOK As ListObject: Set loOK = wsOK.ListObjects(1)
    Dim lcOK As ListColumn: Set lcOK = loOK.ListColumns(1)
    Dim rgOK As Range: Set rgOK = lcOK.DataBodyRange
    
    Dim cell As Range, Student, MatchOK, r As Long, IsNotStudentAdded As Boolean
    
    For Each cell In rgStudents.Cells
        If rgOK Is Nothing Then
            IsNotStudentAdded = True
        Else
            MatchOK = Application.Match(cell.Value, rgOK, 0)
            If IsError(MatchOK) Then IsNotStudentAdded = True
        End If
        If IsNotStudentAdded Then
            Student = cell.Value
            For r = 1 To SportsCount
                loOK.ListRows.Add.Range.Resize(, 2).Value _
                     = Array(Student, Sports(r, 1))
            Next r
            IsNotStudentAdded = False
            Set rgOK = lcOK.DataBodyRange
        End If
    Next cell

    MsgBox "Students added.", vbInformation

End Sub

CodePudding user response：

So I decided to completely change my macro to avoid any error :

Sub Macro7()
Dim N, S, i, j
Application.ScreenUpdating = False
N = Range("Tableau1"): S = Sheets("Sports").Range("C3:C5").Value
With Range("Tableau2").ListObject
  If .ListRows.Count > 0 Then .DataBodyRange.Delete
  For Each i In N
    For Each j In S
      .ListRows.Add.Range.Resize(, 2) = Array(i, j)
    Next
  Next
End With
End Sub

Copy to Excel Table (ListObject)

Copy to Excel Table (`ListObject`)