Compile Error Object Required (Excel VBA)

[mfreem02]

Hi All,

Been a while since I had to ask for help but HELP! I am trying to find the first blank row in a named TABLE. My code works for everything except the last WITH statement which is checking the last table. If you notice, all of the WITH statements are the same except for changing the table objects and the row identifiers. What is frustrating me is that the compile error is only being thrown for the last WITH statement against the variable lRowMgmtComment. I have already tried not using the SET statement for the variables but that throws a different error into the mix. If y'all can take a look and let me know why the first three WITH statements work fine but the last errors out, I would appreciate it.

Option Explicit


'THIS IS THE ONE
Sub NewerLastRow()


Dim Axis1, PICS1, Axis_Comments, Mgmt_Comments As ListObject
Dim sFileName As String 'show the open dialog and pass the selected file name to the string variable "sFileName"
Dim WB, wkbk As Workbook
Dim wkshtData, wkshtComment As Worksheet
Dim lRowAxisData, lRowAxisComment, lRowMgmtData, lRowMgmtComment As Long


Set wkbk = Workbooks("OB Analysis.xlsm")
Set wkshtData = Worksheets("Survey_Import_Data")
Set wkshtComment = Worksheets("Survey_Import_Comments")
Set Axis1 = wkshtData.ListObjects("Axis1")
Set PICS1 = wkshtData.ListObjects("PICS1")
Set Axis_Comments = wkshtComment.ListObjects("Axis_Comments")
Set Mgmt_Comments = wkshtComment.ListObjects("Mgmt_Comments")


With Axis1.Range.Columns(1)
     Set lRowAxisData = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlNext) 'finds the first empty row in the table
     If Not lRowAxisData Is Nothing Then
        'do stuff`
        Debug.Print "first empty row in Axis data is " & lRowAxisData.Row 'First empty row
    End If
End With
With PICS1.Range.Columns(1)
    Set lRowMgmtData = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlNext) 'finds the first empty row in the table
    If Not lRowMgmtData Is Nothing Then
        'do stuff`
        Debug.Print "first empty row in Mgmt data is " & lRowMgmtData.Row 'First empty row
    End If
End With
With Axis_Comments.Range.Columns(1)
    Set lRowAxisComment = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlNext) 'finds the first empty row in the table
    If Not lRowAxisComment Is Nothing Then
        'do stuff`
        Debug.Print "first empty row in Axis comments is " & lRowAxisComment.Row 'First empty row
    End If
End With
With Mgmt_Comments.Range.Columns(1)
        Set lRowMgmtComment = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlNext) 'finds the first empty row in the table
        If Not lRowMgmtComment Is Nothing Then
        'do stuff`
        Debug.Print "first empty row in Mgmt comments is " & lRowMgmtComment.Row 'First empty row
    End If
End With


End Sub

lRowMgmtComment.jpg

[Paul_Hossler]

1. lRowMgmtComment is Dim-ed as a Long

Dim lRowAxisData, lRowAxisComment, lRowMgmtData, lRowMgmtComment As Long

but you're Set-ing like it's a Range

    Set lRowMgmtComment = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlNext) 'finds the first empty row in the table
    If Not lRowMgmtComment Is Nothing Then
        'do stuff`
        Debug.Print "first empty row in Mgmt comments is " & lRowMgmtComment.Row 'First empty row
    End If

2. On a related (sort of) topic, in

Dim lRowAxisData, lRowAxisComment, lRowMgmtData, lRowMgmtComment As Long

lRowAxisData, lRowAxisComment, and lRowMgmtData are all defaulted to Variant since there's no explicit Type. You need to be wordy and explicit


I'm guessing that you meant

Dim lRowAxisData As Long, lRowAxisComment As Long, lRowMgmtData As Long, lRowMgmtComment As Long

3. I'd uncomment this

'Option Explicit

[snb]

1. in a dynamic table you don't need to know how many rows are filled. The table dynamically adapts itself to the amount of data that is put into the table

2. I replaced the whole code in 'CopySurveyData' by 6 lines:

Sub M_snb()
  For Each it In Sheets(Array("Survey_Import_Data", "Survey_Import_Comments"))
    For Each it1 In it.ListObjects
      c00 = c00 & vbLf & it1.Name & "_" & it1.DataBodyRange.Columns(1).SpecialCells(2).Count
    Next
  Next
         
  MsgBox c00
End Sub

3. The for each method automatically creates objectvariables, so no worries about declaring variables at all. The VBcompiler is much smarter than most of its users.

4. So I found no reason to analyze your code any further

[mfreem02]

THANK YOU PAUL!
I changed it to Dim lRowAxisData As Range, lRowAxisComment As Range, lRowMgmtData As Range, lRowMgmtComment As Range and everything worked. Yes, Option Explicit was supposed to be un-commented but I was experimenting and forgot to do that. If I had gone back to my Excel 2007 VBA Programmers Reference, page 43, Declaring Variable Type, I would have seen that I had fallen into the exact trap they said not to; not declaring a type for each variable even though they are on the same line.
Sure appreciate the help! I knew I was rusty but I didn't know it was that bad!!

[mfreem02]

Thanks snb but this did not work for me. I don't know why but my tables do not dynamically adapt when additional data is copied into them. I tried it manually just to confirm before writing the code. Also, the SpecialCells method did not work on some previous tests as it kept telling me the first blank row in the table was the last row in the table. As for not declaring variables, that is a major error in my book since it leads to more problems than it fixes. That is why I use the Option Explicit in all my programs. I just had it commented out here while troubleshooting.
Appreciate you taking a look at this and providing some suggestions.

[snb]

Why did my code provide exactly the information you were looking for in the file you posted ?
If you analyse the code you will understand why it does.
It only runs with 'option explicit' removed/marked out.
Did you alter anything in the file before running the code ?

Try this alternative

Sub M_snb()
  For Each it In Sheets(Array("Survey_Import_Data", "Survey_Import_Comments"))
    For Each it1 In it.ListObjects
      c00 = c00 & vbLf & it1.Name & "_" & it1.Range.Columns(1).SpecialCells(2).Count
    Next
  Next
         
  MsgBox c00
End Sub