Hello all,
Hoping to get some advice please as I'm struggling to get the following line of code working on the WeeklyChange worksheet containing a table called WklyChange as it always results in :-
Run-time error '1004':
Application-defined or object-defined error
The WklyChange table on the WeeklyChange worksheet has a similar structure to below.
The stripped down code that will result in the error is below: -
I thought I had this working previously so not sure why it is not working.
Thanks for taking a look.
Dave.
Hoping to get some advice please as I'm struggling to get the following line of code working on the WeeklyChange worksheet containing a table called WklyChange as it always results in :-
Run-time error '1004':
Application-defined or object-defined error
VBA Code:
tbl.ListColumns(tbl.ListColumns.Count - 1).DataBodyRange.FormulaR1C1 = "=[@[" & todaysDate & "]]-[@[" & oldDateColumnHeader & "]]"
The WklyChange table on the WeeklyChange worksheet has a similar structure to below.
Title | 15/03/2023 | Weekly Change | 20/03/2023 |
Name 1 | 868 | -160 | 708 |
Name 2 | 859 | -160 | 699 |
Name 3 | 843 | -160 | 683 |
Name 4 | 777 | -68 | 709 |
Name 5 | 333 | 100 | 444 |
The stripped down code that will result in the error is below: -
VBA Code:
Sub MySubroutine()
Dim newTblName As String
Dim todaysDate As String
Dim oldDateColumnHeader As String
todaysDate = Format(Date, "dd-mm-yy")
' Get a reference to the table
Set tbl = ActiveSheet.ListObjects("WklyChange")
newTblName = "Table11"
todaysDate = Format(Date, "dd-mm-yy")
' Get the Old Date Column header
oldDateColumnHeader = tbl.ListColumns(tbl.ListColumns.Count - 2).Range.Cells(1, 1).Value
MsgBox ("oldDateColumnHeader = " & oldDateColumnHeader)
' Update the formulas in the second to last column
MsgBox ("todaysDate = " & todaysDate & " - oldDateColumnHeader = " & oldDateColumnHeader)
tbl.ListColumns(tbl.ListColumns.Count - 1).DataBodyRange.Select
tbl.ListColumns(tbl.ListColumns.Count - 1).DataBodyRange.FormulaR1C1 = "=[@[" & todaysDate & "]]-[@[" & oldDateColumnHeader & "]]"
End Sub
I thought I had this working previously so not sure why it is not working.
Thanks for taking a look.
Dave.