VBA - Errors when running code

rcbricker33

New Member
Joined
Oct 18, 2013
Messages
21
I am trying to reference a table and some columns so that I can adjust their style.

VBA Code:
        If lotbl.Name = "tblAFS" Then
            Range("" & wsU.ListObjects(lotbl.Name) & "" & "[[CBDP_END_AMT]:[CBDP_BB_AMT]]").Style = "Comma"
        Else
            Range("" & wsU.ListObjects(lotbl.Name) & "" & "[CBDP_AMT]").Style = "Comma"
        End If

I need to reference the table dynamically as the worksheet and the table name will change. However, the above code keeps throwing an error 9 when I step through the code and the overall function throws a 1004 if I let it run automatically.

Complete code:

VBA Code:
Sub MOVE_DATA(wsU As Worksheet, wsT As Worksheet, arrSGL() As String, arrSUS() As String)
Dim lotbl As ListObject
Dim locSGL As ListColumn, locSUS As ListColumn
Dim lng As Long, lngROW As Long, lngCOL As Long, lngEND As Long
Dim intST As Integer
Dim rng As Range
Dim str1 As String, str2 As String, strTBL As String
Dim vari As Variant

With wsU
    Set lotbl = wsU.ListObjects(1)
    Set locSGL = lotbl.ListColumns("CBDP_SGL")
    Set locSUS = lotbl.ListColumns("CBDP_SUS")
    lng = locSGL.Range.Column
    
    lotbl.Range.AutoFilter Field:=lng, Criteria1:=arrSGL, Operator:=xlFilterValues
    lng = locSUS.Range.Column
    lotbl.Range.AutoFilter Field:=lng, Criteria1:=arrSUS, Operator:=xlFilterValues
    lngROW = LASTrow(wsU)
    If lngROW = lotbl.HeaderRowRange.Row Then
        Exit Sub
    Else
        lngCOL = LASTCOL(wsU)
        Range("" & wsU.ListObjects(lotbl.Name) & "" & "[[CBDP_UID]:[CBDP_SUS]]").Copy
        lotbl.AutoFilter.ShowAllData
        wsT.Activate
        With wsT
            lngROW = LASTrow(wsT)
            lngCOL = LASTCOL(wsT)
            If Not wsT.Cells(lngROW, lngCOL).Value = "CBDP_SUS" Then
                wsT.Cells(lngROW, lngCOL).Offset(, 1).Value = "CBDP_SUS"
                lngCOL = LASTCOL(wsT)
            End If
        End With
        lngEND = wsT.Cells(lngROW, lngCOL).End(xlToLeft).Column
        str1 = ColumnLetter(lngEND)
        str2 = ColumnLetter(lngCOL)
        intST = lngROW + 1
        wsT.Cells(intST, lngEND).PasteSpecial xlValues
    
        Columns(str1 & ":" & str2).ColumnWidth = 141.91
        Columns(str1 & ":" & str2).EntireColumn.AutoFit
        lngROW = LASTrow(wsT)
        Rows(intST & ":" & lngROW).EntireRow.AutoFit
        Set lotbl = wsT.ListObjects(1)
        str1 = wsU.Name

        Application.CutCopyMode = False
        If lotbl.Name = "tblAFS" Then
            Range("" & wsU.ListObjects(lotbl.Name) & "" & "[[CBDP_END_AMT]:[CBDP_BB_AMT]]").Style = "Comma"
        Else
            Range("" & wsU.ListObjects(lotbl.Name) & "" & "[CBDP_AMT]").Style = "Comma"
        End If
    End If
End With
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top