16

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!

Improve this question
2
  • +1: Very nice question, better than most questions asked recently in this tag. :) Writing up a short answer. Commented Feb 28, 2014 at 20:05
  • I would recommend turning your range of data into a table or listobject, then it will automatically do this for you. If you ever add another column you won't have to update code, excel will do all of it for you from fill down when adding rows, to filling down when adding new calculated columns, no need for VBA. This also gives you the huge advantage of using formula instead of saying =B2 you can use =@ColumnHeader and it will use the value in that column on that row, makes updating formulas alot easier, especially when you have so many formulas to upkeep Commented Feb 28, 2014 at 20:40

2 Answers 2

Reset to default
45

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for the great answer, @Gilgamesh. Any idea how I would incorporate a center cell alignment into this? I've looked at the documentation for HorizontalAlignment, but can't seem to put that together in conjunction with FillDown.
@Hashim Just do something like .Range("C2:E" & LRow).HorizontalAlignment = xlCenter after the line with FillDown. Hope that helps. :)
6

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!

Improve this answer

4 Comments

Thank you for your answer, but the first one is much simpler to me.
@user3233328 Not sure if you actually read my answer, it looks long but to apply it all you do is press 2 buttons and your done, nothing in computers gets much simpler than that. 90% of my answer was showing you the benefit. This method is also going to be EXTREMLY leaps and bounds faster, as it won't have to recalculate every single cell in your formula section of your worksheet, it will only add 1 single formula to one cell as you add them, not the entire column, you have currently 22 calculated columns, and even if you only have 100 rows thats 2,200 cells to be calculated EVERY SINGLE
time you enter something. Where as mine will ALWAYS only calculate the 22 columns and only what has changed. AS your data grows you will notice mine takes half a second while the other answer will take minutes to load.
Since I'm dealing with codes, I would like to use codes for this one as well. But again, thank you and I'll try implement your solution to my other problems.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.