Строка вставки таблицы Excel Listobject вызывает ошибку формулы ячейки

Question

Фон: я создал шаблон Word, который содержит подпрограмму, которая создает массив, который содержит заголовок проекта в первом измерении и заголовок цели во втором измерении. Этот массив транспонируется в таблицу в Excel для использования при создании диаграммы временной шкалы / диаграммы Ганта.

Проблема: Транспонирование помещает информацию массива соответствующим образом в таблицу Excel и расширяет размер этой таблицы по желанию. Ячейка B5 является началом DataBodyRange и началом, куда я хочу вставить информацию массива.

' paste headings from array into excel
xlWS.Range("B5:C" & UBound(gHeadings, 2)) = xlApp.Transpose(gHeadings)

То, что не происходит надлежащим образом, находится в последующих ячейках данных в таблице. Ячейки шкалы времени имеют следующую формулу:

=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,""),"")

Когда транспонирование завершено, строка ячеек, содержащая указанные в формуле ошибки в строке таблицы, которая была исходной последней строкой DataBodyRange. В этой строке, строке 10, формула изменяется с того, что показано выше, на:

=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,""),"")

Любые мысли или понимание того, почему это происходит, и как это предотвратить?

Answer 1

Общий ответ о том, почему таблицы Excel ведут себя так, как я описал, остается без ответа. Но вот мой обходной путь. Скорость удара незначительна.

    ' 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

Это эффективно копирует формулу в каждую ячейку, на которую есть ссылки в таблице, и копирует ячейки, на которые неверно ссылались при вставке строки.