Excel VBA AutoFill Multiple Cells with Formulas

Question

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   amp  Rows Count  End xlUp  Row Range  D2   Formula      L 1  L 2  Range  D2   AutoFill Destination  Range  D2 D   amp  lastRow   Range  E2   Formula      B2 2116  Range  E2   AutoFill Destination  Range  E2 E   amp  lastRow   Range  F2   Formula      D 2  3 SQRT   D 2  1- D 2   2116    Range  F2   AutoFill Destination  Range  F2 F   amp  lastRow   Range  G2   Formula      D 2- 3 SQRT   D 2  1- D 2   2116    Range  G2   AutoFill Destination  Range  G2 G   amp  lastRow   Range  H2   Formula     IF  E2 gt   F2  E2 NA     Range  H2   AutoFill Destination  Range  H2 H   amp  lastRow   Range  I2   Formula     IF  E2 lt   G2  E2 NA     Range  I2   AutoFill Destination  Range  I2 I   amp  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   amp  Range  B2   amp  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

User · Accepted Answer

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   amp  LRow  FillDown  much like what you did   Hope this helps

User · Answer

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

[vba] Excel VBA: AutoFill Multiple Cells with Formulas

Examples related to vba

Examples related to excel