Reputation: 407
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!
Upvotes: 16
Views: 180881
Reputation: 8003
Based on my Comment here is one way to get what you want done:
Start byt selecting any cell in your range and Press Ctrl + T
This will give you this pop up:
make sure the Where is your table text is correct and click ok you will now have:
Now If you add a column header in D it will automatically be added to the table all the way to the last row:
Now If you enter a formula into this column:
After you enter it, the formula will be auto filled all the way to last row:
Now if you add a new row at the next row under your table:
Once entered it will be resized to the width of your table and all columns with formulas will be added also:
Hope this solves your problem!
Upvotes: 6
Reputation: 14179
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!
Upvotes: 45