Problems Referencing Table's DataBodyRange

[19Rohan77]

Hi,

To be honest, I am unsure what has happened overnight, as this was working! Now however, I am getting 'runtime error 9 -subscript out of range'. Bizarre, even in old versions of this file that previously worked, they no longer do. Please forgive the use of "select" or "activesheet", the code was written when I knew even less than I do now!

To explain, the following is the relevant snippet from reels of vba. I am using VBA to build a costing spreadsheet that places items from a table on a different sheet ("Data"), under relevant headings on another sheet. It's slow but it has previously trundled through moving DataBodyRange members based on a columns heading match (cell.value from previous sheet) quite happily.

Now however I get out of range. I have tried increasing the specificity and checking table / sheet names but I still keep getting the same thing. Any hints on what excel might be up to and what I need to change would be greatly appreciated.

Cheers

Rohan

ps. I think my set rangename line is actually redundant as I don't use the name elsewhere. I think it's an artifact

For Each cell In RngA

   If cell.Style = "Heading 4" Then
    
        Sheets("Data").Select
    
        On Error Resume Next
    
            'Copy only cells with values from under the appropriate heading (Cell.Value)
    Set rangename = ActiveSheet.ListObjects("System_Section_Content").listcolumns(cell).Value
    
            With ActiveSheet.ListObjects("System_Section_Content").listcolumns(cell.Value).DataBodyRange
            
                On Error Resume Next
                                                  
                        Set rngChange = .SpecialCells(xlCellTypeConstants)
                            
                rngChange.Copy
                
                On Error GoTo 0
                
            End With

 

[pbornemeier]

Before referencing the DBR cheeck to see that it exists as per this sample:

Sub TblHasDBR()
    
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("tblTest")
    Dim sOutput As String
    
    If Not tbl.DataBodyRange Is Nothing Then
        Debug.Print tbl.DataBodyRange.Rows.Count
    Else
        Debug.Print "No DBR"
    End If
    
End Sub

 

[19Rohan77]

Thanks very much for your prompt reply pbornemeier, appreciated. I will try your suggestion when next in front of it. Interestingly, my boss can get the code to execute happily (exactly the same file in the same location) on his PC. Is this at all diagnostic for what might be happening?

Cheers

Rohan

 

[pbornemeier]

Not sure how that is happening. If the table has no DBR then the code should fail when it tries to reference it.

 

[RoryA]

Which line actually causes the error?

 

[19Rohan77]

Hi Phil and Rory, thanks for the continued support. I ended up 'fixing' it, by removing the following line which I assume was originally intended to give a name to the variable, however as I referenced explicitly, it was surplus. Doesn't explain why excel stopped 'liking it' overnight...?

Set rangename = ActiveSheet.ListObjects("System_Section_Content").listcolumns(cell).Value

Cheers

Rohan

 

[Rick Rothstein]

Set rangename = ActiveSheet.ListObjects("System_Section_Content").listcolumns(cell).Value

The Set keyword is used to assign an object to a variable... that .Value on the end of the statement means you are trying to assign a value, not an object, to the rangename variable. Normally, I would say remove the .Value but given your variable's name, I am kind of thinking the keyword Set should be removed instead. It is hard to know your intention here because you only provided a snippet of your code, so we have to guess at what your code is trying to do.

 

[DEksel]

This one

    If Not tbl.DataBodyRange Is Nothing Then
        Debug.Print tbl.DataBodyRange.Rows.Count
    Else
        Debug.Print "No DBR"
    End If

Always return tbl.DataBodyRange.Rows.Count
I am using the next wild way:

Set s = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
err1004Handling:
    If Err.Number = 1004 Then
        Target.Value = vbNullString
        UnhideRows_and_OpenTable wsS, tbl
        MessageBox "No Data.", "excl"
    End If

No filter returns (for me: Excel 2016) always Error 1004.
Maybe you have idea why it not works?

Before referencing the DBR cheeck to see that it exists as per this sample:

Sub TblHasDBR()
 
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("tblTest")
    Dim sOutput As String
 
    If Not tbl.DataBodyRange Is Nothing Then
        Debug.Print tbl.DataBodyRange.Rows.Count
    Else
        Debug.Print "No DBR"
    End If
 
End Sub

 

[DEksel]

I wonder, is it possible...
If I refer to table in some cell outside table, I am writing something like next:

=Table1[[#Totals],[Balance]]

Can I use DataBodyRange here:
instead of

=COUNTIF(I5:I63,1)

to use something as

=COUNTIF(Table1[[Status],[DataBodyRange]],1)

or simillar?

Thanks in advance, Rick.

The Set keyword is used to assign an object to a variable... that .Value on the end of the statement means you are trying to assign a value, not an object, to the rangename variable. Normally, I would say remove the .Value but given your variable's name, I am kind of thinking the keyword Set should be removed instead. It is hard to know your intention here because you only provided a snippet of your code, so we have to guess at what your code is trying to do.