Excel VBA:使用公式自动填充多个单元格 [英] Excel VBA: AutoFill Multiple Cells with Formulas
问题描述
Application.ScreenUpdating = False
lastRow = Range(B& Rows.Count) .End(xlUp).Row
Range(D2)。Formula == $ L $ 1 / $ L $ 2
范围(D2)。自动填充目标:=范围(D2: D& lastRow)
范围(E2)。Formula == $ B2 / 2116
范围(E2)。自动填充目标:=范围(E2: ($ D $ 2 *(1- $ D $ 2))/ 2116)= $ D $ 2 )
范围(F2)。自动填充目标:=范围(F2:F& lastRow)
范围(G2)。Formula == $ D $ 2 - (3 * SQRT(($ D $ 2 *(1- $ D $ 2))/ 2116))
范围(G2)。自动填充目标:=范围(G2:G& lastRow)
Range(H2)。Formula == IF($ E2> = $ F2,$ E2,NA())
范围(H2)自动填充目标: = Range(H2:H& lastRow)
范围(I2)。Formula == IF($ E2 <= $ G2,$ E2,NA())
范围(I2)。自动填充目标:=范围(I2:I& lastRow)
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
何wever,在主要工作簿中,有15种不同的公式,我希望它每次新数据输入时自动填充。我有多个主要工作簿,公式不是常数。为每个公式插入上面的代码是一个痛苦。有没有办法使程序自动拖动?在主要工作簿中,我已经写出了公式。我尝试了许多不同的代码来使其自动填充,但是到目前为止,只有一个在不给我错误的情况下工作。我尝试使用这样的或类似的版本,但没有一个是正常工作:
使用wbList.Sheets(Attribute - 10 mil stop)
lastRow = Worksheets(ActiveSheet.Name).Range(B2)。Rows.Count
'Worksheets(ActiveSheet.Name).Range(Selection,Selection.End(xlDown) )。选择
工作表(ActiveSheet.Name).Range(D2:I2)。选择
Selection.AutoFill目的地:=范围(D2:I& Range(B2& Rows.Count).End(xlDown).Row)
结束
我搞砸了代码如此之多。我甚至不知道是否会这样。感谢您的帮助!
您正在寻找的方法是 FillDown
。另一种方式,所以你不必每次踢你的头是将公式存储在字符串数组中。结合它们为您提供了众多输入公式的强大方法。代码如下:
Sub FillDown()
Dim strFormulas(1 To 3)As Variant
使用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
:
结果为: .Range(C2: E11)。FillDown
:
当然,您可以通过将最后一行存储到变量中使其变为动态,将其转换为 .Range(C2:E& LRow).FillDown
,很像你所做的那样。
希望这有帮助!
I have big amount of data that I collected from different files. In this main workbook, I have different types of formulas for every cells. In range A to F is where the data from other files are collected. In range H to AC, I have the formula that I auto fill by dragging it down manually every time new data is entered. The code below is what I used and it only have 6 different formulas that I want to auto fill.
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
However, in the main workbook there is like 15 different formulas that I want it to auto fill every time new data enters. I have multiple main workbook, and the formula is not constant. Inserting the code above for every formula is a pain. Is there a way that can make the program to drag it down automatically? In the main workbook, I have the formulas written out already. I tried many different codes to make it auto fill, but so far the one above is the only one that working without giving me errors. I tried using something like this or similar version to this one, but none is working:
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
I messed around with the code so much. I don't even know if it's suppose to be like that. Thank you for helping!
The approach you're looking for is FillDown
. Another way so you don't have to kick your head off every time is to store formulas in an array of strings. Combining them gives you a powerful method of inputting formulas by the multitude. Code follows:
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
Screenshots:
Result as of line: .Range("C2:E2").Formula = strFormulas
:
Result as of line: .Range("C2:E11").FillDown
:
Of course, you can make it dynamic by storing the last row into a variable and turning it to something like .Range("C2:E" & LRow).FillDown
, much like what you did.
Hope this helps!
这篇关于Excel VBA:使用公式自动填充多个单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!