Excel Table, using VBA to change data in a column-CodePudding

I have a Table with a column that I added, that checks the date of that row against a formula. The result of that formulae is TRUE or FALSE and a subsequent Pivot Table Sums a value in the TRUE rows. I Introduced it to get what is called a Rolling Total Income. There are two formula and I want to swap from one to another using VBA. The formula are:

=IF([@Date] = "", "", AND([@Date]>=EOMONTH(TODAY(),-13) 1,[@Date]<EOMONTH(TODAY(),-1)))

for the Rolling data.

=IF(AND([@Date] >=G1,[@Date] <=H1),"TRUE","FALSE")

for the Sum of Income between G1 and H1, the financial year start and end dates.

The VBA I have tried is as follows:

Set lo = Sheet2.ListObjects(1)   'Sheet2 is the Income sheet see Project list
lo.Parent.Activate               'Activate sheet that Table is on
Set lColName = lo.ListColumns(9)
lColName.DataBodyRange.Cells(1).Formula = [=IF(AND([@Date] >=G1,[@Date] <=H1),"TRUE","FALSE")]

Running the above errors and gives #VALUE in the first cell in that column, and doesn't ripple through the rest of the column.

What have I done wrong?

CodePudding user response：

Try

lColName.DataBodyRange.Formula = "=IF(AND([@Date] >=$G$1,[@Date] <=$H$1),""TRUE"",""FALSE"")"

CodePudding user response：

Thanks, those changes have sorted it.