How can I insert variable into formula in VBA

by

your actual goal is unclear

you may want to start form this code

Sub test()
    Dim i As Integer

    For i = 1 To 10
       cells(i, 4).Formula = "=Sum(E" & i & ":E15)"
    Next
End Sub

and adjust it to your needs, knowing that:

  • it currently writes in cells “D1:D10”

    since cells(i, 4) references a cell in 4th column (i.e.: column “D”) 4 and i row, and we’re inside a loop where i is looping through 1 to 10

    so if:

    • you want to reference a different column then just change 4 to the proper column index

    • you want to reference a different row then just change i to the proper row index (may be some i+2 if you need to iterate through 1 to 10 but start writing from row 3)

  • the formula written in those cells is:

    =SUM(E1:E15) in D1,

    =SUM(E2:E15) in D2,

    ….

    =SUM(E10:E15) in D10.

    so just change "=Sum(E" & i & ":E15)" to your actual needs

More Related Contents:

  1. VBA to open Excel hyperlink does not work when hyperlink generated with a formula
  2. to make this code simpler
  3. VBA code takes long time to execute in excel
  4. Wait until ActiveWorkbook.RefreshAll finishes – VBA
  5. Code in VBA loops and never ends. How to fix this?
  6. Excel column number from column name
  7. Pop up the Excel Statusbar?
  8. VBA – destroy a modeless UserForm instance properly
  9. How To Speed Up VBA Code
  10. Execute a command in command prompt using excel VBA
  11. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  12. Copy data from another Workbook through VBA
  13. Excel VBA Load worksheet in Function
  14. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  15. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  16. Load csv file into a VBA array rather than Excel Sheet
  17. Insert line break in wrapped cell via code
  18. Change color of certain characters in a cell
  19. Concatenate column headers if value in rows below is non-blank
  20. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  21. VBA Macro crashes after 32000 rows
  22. How to create dynamic variable names VBA
  23. How to break a long string into multiple lines
  24. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  25. simple vba code gives me run time error 91 object variable or with block not set
  26. How do I open a file if I only know part of the file name?
  27. VBA recognizing workbook by partial name
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment