Excel VBA: автозаполнение нескольких ячеек с формулами
У меня есть большой объем данных, которые я собрал из разных файлов. В этой основной книге у меня разные типы формул для каждой ячейки. В диапазоне от A до F находятся данные из других файлов. В диапазоне от H до AC у меня есть формула, которую я автоматически заполняю, перетаскивая ее вручную каждый раз, когда вводятся новые данные. Код ниже - это то, что я использовал, и у него есть только 6 разных формул, которые я хочу автозаполнять.
Application.ScreenUpdating = False
lastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=$L$1/$L$2"
Range("D2").AutoFill Destination:=Range("D2:D" & lastRow)
Range("E2").Formula = "=$B2/2116"
Range("E2").AutoFill Destination:=Range("E2:E" & lastRow)
Range("F2").Formula = "=$D$2+(3*SQRT(($D$2*(1-$D$2))/2116))"
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)
Range("G2").Formula = "=$D$2-(3*SQRT(($D$2*(1-$D$2))/2116))"
Range("G2").AutoFill Destination:=Range("G2:G" & lastRow)
Range("H2").Formula = "=IF($E2>=$F2,$E2,NA())"
Range("H2").AutoFill Destination:=Range("H2:H" & lastRow)
Range("I2").Formula = "=IF($E2<=$G2,$E2,NA())"
Range("I2").AutoFill Destination:=Range("I2:I" & lastRow)
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
Однако в основной книге есть 15 различных формул, которые я хочу, чтобы она автоматически заполнялась каждый раз, когда вводились новые данные. У меня есть несколько основных книг, и формула не является постоянной. Вставка кода выше для каждой формулы - боль. Есть ли способ, который может заставить программу автоматически перетащить ее? В основной книге у меня уже есть формулы. Я пробовал много разных кодов, чтобы сделать его автозаполнением, но пока что один выше, который работает без ошибок. Я попытался использовать что-то вроде этой или аналогичной версии для этого, но никто не работает:
With wbList.Sheets("Attribute - 10 mil stop")
lastRow = Worksheets(ActiveSheet.Name).Range("B2").Rows.Count
'Worksheets(ActiveSheet.Name).Range(Selection, Selection.End(xlDown)).Select
Worksheets(ActiveSheet.Name).Range("D2:I2").Select
Selection.AutoFill Destination:=Range("D2:I" & Range("B2" & Rows.Count).End(xlDown).Row)
End With
Я так много перепутал с кодом. Я даже не знаю, предположительно ли это так. Спасибо, что помогли!
Ответы
Ответ 1
Подход, который вы ищете, FillDown
. Другой способ, чтобы вам не приходилось ударять головой каждый раз, - это хранить формулы в массиве строк. Объединение их дает вам мощный метод ввода формул множеством. Код следует:
Sub FillDown()
Dim strFormulas(1 To 3) As Variant
With ThisWorkbook.Sheets("Sheet1")
strFormulas(1) = "=SUM(A2:B2)"
strFormulas(2) = "=PRODUCT(A2:B2)"
strFormulas(3) = "=A2/B2"
.Range("C2:E2").Formula = strFormulas
.Range("C2:E11").FillDown
End With
End Sub
Скриншоты:
Результат по строке: .Range("C2:E2").Formula = strFormulas
:
![enter image description here]()
Результат по строке: .Range("C2:E11").FillDown
:
![enter image description here]()
Конечно, вы можете сделать его динамическим, сохранив последнюю строку в переменной и превратив ее в нечто вроде .Range("C2:E" & LRow).FillDown
, похожее на то, что вы сделали.
Надеюсь, это поможет!
Ответ 2
Основываясь на моем комментарии, вот один из способов получить то, что вы хотите сделать:
Начните байт, выбирая любую ячейку в вашем диапазоне и нажмите Ctrl + T
Это даст вам всплывающее окно:
![enter image description here]()
убедитесь, что текст вашей таблицы верен и нажмите "ОК", теперь у вас будет:
![enter image description here]()
Теперь, если вы добавите заголовок столбца в D, он будет автоматически добавлен в таблицу до последней строки:
![enter image description here]()
Теперь, если вы введете формулу в этот столбец:
![enter image description here]()
После ввода формулы формула будет автоматически заполнена до последней строки:
![enter image description here]()
Теперь, если вы добавили новую строку в следующую строку под своей таблицей:
![enter image description here]()
После ввода его размер будет изменен до ширины вашей таблицы, а также будут добавлены все столбцы с формулами:
![enter image description here]()
Надеюсь, что это решает вашу проблему!