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

Предыстория: я создал шаблон 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,""),"")

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


excel vba ms-word excel-tables
person Ron Kochanowski    schedule 18.10.2014    source источник
comment
Точная проблема немного неясна. Меняется ли формула - единственная проблема? Если нет, просьба уточнить. Что касается формулы, разве вы не можете добавить «$» перед частями, которые не хотите изменять? (Например, вы не хотите, чтобы H10 изменялся на H119, поэтому измените его на $ H $ 10) Если это не вариант, не могли бы вы предоставить полные процедуры, которые ваш код выполняет с массивом, и даже пример некоторых данных массива ?   -  person David Rachwalik    schedule 19.10.2014
comment
Приношу свои извинения за любую путаницу. Позвольте мне попытаться уточнить. При переносе массива в таблицу строки автоматически вставляются в таблицу. Эта часть кода, показанная выше, работает, как задумано. Проблема заключается в следующем: в шаблоне уже есть формула ячейки в ячейках, начинающихся со столбца H. Когда новые строки вставляются в таблицу с транспонированием, эта формула должна копироваться во все новые строки. Это происходит со всеми строками вставки, за исключением указанной строки. По какой-то причине Excel изменил ссылку на эту строку, чтобы она указывала на последнюю строку таблицы.   -  person Ron Kochanowski    schedule 19.10.2014

Ответы (1)


arrow_upward
0
arrow_downward

На общий ответ относительно того, почему таблицы 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

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

person Ron Kochanowski    schedule 19.10.2014