Скопируйте формулу вниз по динамическому столбцу (без заголовка)

У меня есть таблица, которая будет регулярно обновляться. Поскольку будут вставлены столбцы, у меня нет фиксированного диапазона.

Я нашел способ найти определенное значение (x) и выбрать ячейку ниже. Мне удалось обновить все необходимые формулы и распечатать их в ячейке под заголовком.
Работает до момента копирования.

Я попытался скопировать формулу вниз по столбцу:

Sub Sum_three_months()

    Set Three_months = Range("A1:ZZ10000").Find("x")
    Three_months.Select
    FormularCell = ActiveCell.Offset(1, 0).Select
    Selection.Resize(Selection.Rows.Count, _
    Selection.Columns.Count).Select

    ActiveCell.FormulaR1C1 = "=SUM(R[-0]C[-4]:R[-0]C[-2])"
    
    Sum_six_months ' starts the next update, but has the same format as above
End Sub

Я пробовал Autofill и FillDown, но с автозаполнением я получаю сообщение об ошибке с диапазоном/выбранной ячейкой, а с заполнением просто копирует заголовок.


excel range vba dynamic-columns copying
person Djerun    schedule 10.07.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Попробуйте этот код, пожалуйста:

Sub Sum_three_months()
  Dim sh As Worksheet, Three_months As Range, rngLast As Range, x As String, lastCol As Long
 
  Set sh = ActiveSheet 'use here your sheet
  lastCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column
  x = "x" 'use here your search string...
  Set Three_months = sh.Range(sh.Cells(1, 1), sh.Cells(1, lastCol)).Find(x)
  If Not Three_months Is Nothing Then
      Set rngLast = sh.Cells(sh.Cells(Rows.count, Three_months.Offset(0, -4).Column).End(xlUp).row, Three_months.Column)
    
      sh.Range(Three_months.Offset(1, 0), rngLast).formula = _
                   "=SUM(" & sh.Range(Three_months.Offset(1, -4), Three_months.Offset(1, -2)).address(0, 0) & ")"
  Else
     MsgBox "Not a ""x"" range could be found...": Exit Sub
  End If
    
 Sum_six_months ' starts the next update, but has the same format as above
End Sub
person FaneDuru    schedule 10.07.2020
comment
@Djerun: Вы сохранили объявления переменных, которые я использовал в коде? В любом случае, пожалуйста, обновите страницу (эту) и скопируйте последнюю версию, где все диапазоны ссылаются на один и тот же лист (активный лист в моем коде, но вы можете выбрать тот, который вам нужен). Я использовал ваши кодовые базы, но было бы неплохо проверить, не является ли Three_months ничем... Я адаптирую код... - person FaneDuru; 10.07.2020
comment
Не совсем. Ваш код заменяет мой заголовок суммой ячеек C2:E2, но это не те ячейки, которые мне нужны, и я также не копирую формулы... Редактировать: я скопировал ваш код в свою книгу и выполнил его. - person Djerun; 10.07.2020
comment
@Djerun: Да, извините ... Я использовал конкретный случай, который тестировал. Пожалуйста, обновите страницу и попробуйте использовать обновленный код. Вы все еще получаете сообщение об ошибке в этой строке кода? - person FaneDuru; 10.07.2020
comment
Хорошо, теперь ваша сумма находится в правой ячейке. Но он не копирует столбец и Range("C2:E2") is wrong. It needs to take the sum of three cells next to it´s left cell. So if the formular is in "F2" it shall skip "E2" and take the sum of "B2:D2" . That´s why I used (R[-0]C[-4]:R[-0]C[-2]``` Редактировать: я попробую! - person Djerun; 10.07.2020
comment
@Djerun: Попробуйте обновленный код, пожалуйста. На самом деле, вы проверяли мой код, используя Offset (-4 и -2)? Есть ли на вашем листе какая-либо строка до ячейки x? Если нет, то по какой колонке считать, что падать? - person FaneDuru; 10.07.2020
comment
@Djerun: Попробуйте обновленный код, пожалуйста. На самом деле, вы проверяли мой код, используя Offset (-4 и -2)? Есть ли на вашем листе какая-либо строка до ячейки x? Если нет, то по какой колонке считать, что падать? По смыслу вашего вопроса я предполагал, что есть строки вниз и они будут переписаны. Если нет, укажите, на основе какого столбца код должен вычислять последнюю строку для заполнения. - person FaneDuru; 10.07.2020
comment
Могу ли я отправить вам пример файла на ваш почтовый адрес, который я вижу в вашем профиле? Может быть, вам это легче понять, чем мое небрежное объяснение... - person Djerun; 10.07.2020
comment
@Djerun: Похоже, у меня проблема с Outlook. Поскольку я решу это, попробуйте обновленный код. Теперь он вычисляет последнюю заполняемую строку в соответствии с последней ячейкой в ​​Offset(0, -4)... Я получаю ее, и мой последний код делает именно то, что (я понимаю) вам нужно. Вы проверили это? И теперь я вижу, что ваша строка поиска находится в первой строке. Всегда ли он будет в первом ряду? - person FaneDuru; 10.07.2020