0

I have this code here that keep giving an error code that says Object required at the listobject line

Dim table As ListObject
Set table = wks.ListObjects("TableName")

Dim newRow As ListRow
Set newRow = table.ListRows.Add

Below is the full code, i have no idea what object is missing, can anyone advice me on this? thanks so much!

Sub GenerateData()

Dim sheet As Worksheet
Set sheet = CreateOutputSheet(ActiveWorkbook)

Dim basePath As String
basePath = Environ$("USERPROFILE") & "\Desktop\Tryout\"

Dim baseFolder As Scripting.Folder
With New Scripting.FileSystemObject
    Set baseFolder = .GetFolder(basePath)
End With

Dim table As ListObject
Set table = wks.ListObjects("TableName")

Dim newRow As ListRow
Set newRow = table.ListRows.Add

 Dim source As Range
    Set source = wksData.Range("A:A")

    PopulateIfFound source, "  testtest         : ", newRow, 1
    PopulateIfFound source, "  testflyy         : ", newRow, 2
    PopulateIfFound source, "  testflyy         : ", newRow, 3
    PopulateIfFound source, "  Started at: ", newRow, 4
    If Not PopulateIfFound(source, "SmartABC ", newRow, 9) Then
        PopulateIfFound source, "smart_mfh revision", newRow, 9
    End If
    If Not PopulateIfFound(source, "ERROR: ABC ", newRow, 10, True) Then
        PopulateIfFound source, "ERROR: DEF ", newRow, 10
    End If

End Sub

Private Sub AddColumnHeaders(ByVal sheet As Worksheet)
    sheet.Range("A1:K1") = Array( _
         "Test", "Temp", "Type", "StartDate", _
         "FileName", "No", "EndDate", "Month", _
         "Smart", "Errors", "ErrorCellAddress")
End Sub

Private Function CreateOutputSheet(ByVal book As Workbook) As Worksheet
    Dim sheet As Worksheet
    Set sheet = book.Worksheets.Add(After:=book.Worksheets(book.Worksheets.Count))
    AddColumnHeaders sheet
    Set CreateOutputSheet = sheet
End Function

Private Function PopulateIfFound(ByVal source As Range, ByVal value As String, ByVal row As ListRow, ByVal writeToColumn As Long, Optional ByVal writeAddressToNextColumn As Boolean = False) As Boolean
    Dim result As Range
    Set result = source.Find(value, LookIn:=xlValues)
    If Not result Is Nothing Then
        Dim cell As Range
        Set cell = row.Range.Cells(ColumnIndex:=writeToColumn)
        cell.value = result.value
        If writeAddressToNextColumn Then
            cell.Offset(ColumnOffset:=1).value = result.Address
        End If
        PopulateIfFound = True
    End If
End Function
Improve this question
1
  • You have never set wks before your line. That would be your unqualified and missing object. Did you actually needed Sheet? Commented Dec 11, 2019 at 8:39

1 Answer 1

Reset to default
1

You are using wks variable, which is not assigned anywhere.

In order to use variable best practice is to:

  1. declare it using Dim keyword

  2. Assign it with = operator (quite obvious)

So, for example, for Worksheet object it would be:

Dim wks As Worksheet
' reference types ned Set keyword when assigning
Set wks = Worksheets("Sheet1") ' just example
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Ah yes sorry for not including it, i have tried Dim wks As Worksheet and it gives me Object Variable or With block Variable not set , though when i tried adding another line of yours Set wks = Worksheets("Sheet1") It gives me Subscript out of range Is it perhaps because my program creates a new sheet everytime i run it and it will never be an exact sheet name?
So try Worksheets(1)
Still doesn't work , it gives an error subscript out of range
@lalalisa, the subscipt out of range error is returned because you don't have a worksheet called "Sheet1".
@lalalisa, then you don't get that error on Set wks = Worksheets("Sheet1") but on Set table = wks.ListObjects("TableName") most likely, meaning you don't have a ListObject called "TableName" on that sheet. As it is, it seems as though you needed the Sheet object but since you just created it, it doesn't hold a ListObject.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.