1

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?

eli-k
  • 10,898
  • 11
  • 40
  • 44
Ron Kochanowski
  • 23
  • 3
  • The exact problem is a little unclear. Is the formula changing the only problem? If not, please elaborate. In regards to the formula, are you not able to add '$' in front of the parts you don't want to change? (E.g. you don't want H10 to change to H119 so change it to $H$10) If that is not an option, can you please provide the full procedures your code is going through with the array and even an example of some array data? – David Rachwalik Oct 19 '14 at 04:26
  • My apologies for any confusion. Let me try to clarify. Transposing the array into a table will automatically insert rows into the table. This portion of the code, shown above is working as intended. The issue is this: The template already has cell formula in cells beginning at column H. When the new rows are inserted into the table with the transpose, these formula are supposed to copy to all of the new rows. This occurs with all of the insert rows with the exception of the row indicated. For some reason, Excel changed the reference on this one row to point to the last row of the table – Ron Kochanowski Oct 19 '14 at 12:22

1 Answers1

1

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.

Ron Kochanowski
  • 23
  • 3