Using VBA, I am adding two formulas to cells in a table on my Excel sheet. They are getting added to the second row the the Excel sheet which is the first data row of the table (the first row of the Excel sheet is the headers). Below is a snippet of the code.
' Insert a new column to the left of "Grand Total"
Set newCol = tbl.ListColumns.Add(colPosition)
' Name the new column "MX" where X is the count of the total motnhs on the project
newColName = "M" & newCol.Index - 2
newCol.Name = newColName
newCol.DataBodyRange.Cells(2, 1).Value = "=IFERROR(VLOOKUP(A2, INDIRECT(" & Chr(34) & Chr(39) & Chr(34) & " & C$1 & " & Chr(34) & Chr(39) & Chr(34) & " & ""!A:B""), 2, FALSE), """")"
newCol.DataBodyRange.Cells(2, 2).Value = "=SUM(C2:" & ColLetter(newCol.Index) & "2)"
Now, the formulas are added to the correct cells and column of the table as they should be. However, the cells that are being referenced in the formula are adjusting to one row up on their own. So for example, A2 is becoming A1. Here is a screenshot of what I mean (note that the C$1 reference is correct, it is the A1 reference that is wrong and should be A2 as is specified in the code).
I don’t believe I can use absolute references as I need the formula to automatically adjust and continue down the table (which is does correctly, just starting with references to one row above where I want it to). Is there a way to force Excel to start with the cell I tell it to in the code or is this just a quirk of tables that I need to work around?
2
GSerg already wrote in the comments what causes the behaviour. To explain: DataBodyRange is the Range in a table or column holding the data, without the header row. So DataBodyRange.Cells(2, 1) is refering to the 2nd datarow – when your table starts in row 1 of the sheet, that’s row 3.
Now when you enter a formula in any cell inside a table, this formula will be copied to all (data-)cells of that column. When you write A2 in the formula of the 2nd row, it will write A1 into the 1st row.
So, either use newCol.DataBodyRange.Cells(1, 1) (and newCol.DataBodyRange.Cells(1, 2) for the second formula).
Or use the Range-property. The Range-property uses the full range, including the header row: newCol.Range.Cells(2, 1)
Sidenote: When writing formulas into a cell, don’t use the Value property but the Formula or Formula2 property.
newCol.DataBodyRange.Cells(1, 1).Formula2 = ...