Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA: AutoFill Multiple Cells with Formulas

Tags:

excel

vba

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!

like image 339
user3233328 Avatar asked Feb 28 '14 20:02

user3233328


People also ask

How do I use AutoFill in macro?

Step 1: For this, we need a module. Go to Insert menu and select the module as shown below. Step 2: After we get a module, in that write subcategory of VBA Autofill or in any other name of your choice as shown below. Step 3: Select the range of the cells which we need to drag or extent for autofill.

How do you auto fill cells with formulas?

Fill formulas into adjacent cellsSelect the cell with the formula and the adjacent cells you want to fill. Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.


2 Answers

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:

enter image description here

Result as of line: .Range("C2:E11").FillDown:

enter image description here

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!

like image 57
NullDev Avatar answered Sep 21 '22 07:09

NullDev


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:

enter image description here

make sure the Where is your table text is correct and click ok you will now have:

enter image description here

Now If you add a column header in D it will automatically be added to the table all the way to the last row:

enter image description here

Now If you enter a formula into this column:

enter image description here

After you enter it, the formula will be auto filled all the way to last row:

enter image description here

Now if you add a new row at the next row under your table:

enter image description here

Once entered it will be resized to the width of your table and all columns with formulas will be added also:

enter image description here

Hope this solves your problem!

like image 21
user2140261 Avatar answered Sep 22 '22 07:09

user2140261