Excel VBA: AutoFill Multiple Cells with Formulas

by

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!

More Related Contents:

  1. Loop through files in a folder using VBA?
  2. How to error handle 1004 Error with WorksheetFunction.VLookup?
  3. How can Excel VBA open file using default application
  4. How to compare two entire rows in a sheet
  5. How to make Excel VBA variables available to multiple macros?
  6. Run same excel macro on multiple excel files
  7. how to detect whether VBA excel found something?
  8. VBA : save a file with UTF-8 without BOM
  9. Excel tab sheet names vs. Visual Basic sheet names
  10. Saving a Excel File into .txt format without quotes
  11. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  12. Determine if cell contains data validation
  13. How to add data validation to a cell using VBA
  14. How to do late binding in VBA?
  15. How to break long string to multiple lines
  16. How to copy a chart from Excel to PowerPoint?
  17. vba paste not working
  18. mac excel vba loop : from list & then export as pdf
  19. Can Advanced Filter criteria be in the VBA rather than a range?
  20. In Excel VBA, what is the way to check if the web page is fully loaded?
  21. Excel Useform: How to hide application but have icon in the taskbar
  22. Reading Web Pages using Excel VBA
  23. Excel VBA: auto click and open file from website
  24. Calling regasm without administrative rights for COM interop in Excel VBA
  25. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment