Background: I've created a Word template that contains a routine that creates an array which holds a Project title in the first dimension and a Goal title in the second dimension. This array is transposed to a table in Excel to be used in creating a timeline/gantt chart.
Problem: The transpose places the array information appropriately into an Excel table and expands the size of that table as desired. Cell B5 is the beginning of the DataBodyRange and the start of where I want to paste the array information.
' paste headings from array into excel
xlWS.Range("B5:C" & UBound(gHeadings, 2)) = xlApp.Transpose(gHeadings)
What does not occur in the proper manner is in subsequent data cells within the table. Timeline cells have the following formula:
=IF(AND(COLUMNS($H$5:H10)>=$E5,COLUMNS($H$5:H10)<=$F5),IF(COLUMNS($H$5:H10)-$E5<ROUND(($F5-$E5+1)*$G5,0),fillblock,""),"")
When the transpose is finish, the row of cells containing the referenced formula errors on the row of the table that was the original last row of the DataBodyRange. On that row, row 10, the formula gets changed from what's displayed above to:
=IF(AND(COLUMNS($H$5:H119)>=$E10,COLUMNS($H$5:H119)<=$F10),IF(COLUMNS($H$5:H119)-$E10<ROUND(($F10-$E10+1)*$G10,0),fillblock,""),"")
Any thoughts or understanding of why this happens, and how to prevent it?
The overall answer as to why Excel Tables behave in the fashion I've described is not answered. But, here is my workaround. The speed hit is negligible.
' variable to hold the formula and fix the double-quote ("") issue in vba
sEmpty = Chr(34) & Chr(34)
sFormula = "=IF(AND(COLUMNS($H$5:H5)>=$E5,COLUMNS($H$5:H5)<=$F5),IF(COLUMNS($H$5:H5)-$E5<ROUND(($F5-$E5+1)*$G5,0),fillblock," & sEmpty & ")," & sEmpty & ")"
' paste cell formula into the new worksheet
lTblRows = xlLO.DataBodyRange.Rows.Count
xlWS.Range("H5:AK" & lTblRows + 4).Formula = sFormula
This effectively copies the formula into each cell referenced within the table, copying over the cells that got mis-referenced during the row insert.