Automating Excel Formula For Multiple Columns

[PinoyDev]

Good day!

I just want a little help on how to loop from one cell to the end and set a sum Formula. I have Columns From **F to O **and created a Sum formula for column F. Is there a way to make it in a loop and pass only the column name in the Sum Formula to sum up until column O?

My code below to sumup only Column F. I basically need a loop here to sum up up to the column O.

excel_worksheet.Range("F" & (FormulaRow + 1)).Value = "=SUM(F" & FormulaStartRow & ":F" & FormulaRow & ")"
excel_worksheet.Range("F" & (FormulaRow + 1)).RowHeight = 25.5
excel_worksheet.Range("F" & (FormulaRow + 1)).Font.Bold = True

Thanks for helping!

[Mr.M]

I think what you will need is something like this "For i As String = F To O" but not sure if it works with alphabets and a you can notice I didn't add the "Step" because I don't know how in alphabets but in numbers the step would be "1" or "-1".

Then in your code you would need to change "F" to "i".

[PinoyDev]

Thank you for the Idea Mr.M.

I have found this trick working by tweaking alittle bit with Chr function.

Dim ColumnCounter As Int16
Dim SumupStartColumn As String = "F"

For ColumnCounter = 1 To 11
     excel_worksheet.Range(SumupStartColumn & (FormulaRow + 1)).Value = "=SUM(" & SumupStartColumn & FormulaStartRow & ":" & SumupStartColumn & FormulaRow & ")"
     excel_worksheet.Range(SumupStartColumn & (FormulaRow + 1)).RowHeight = 25.5
     excel_worksheet.Range(SumupStartColumn & (FormulaRow + 1)).Font.Bold = True
     SumupStartColumn = Chr(Asc(SumupStartColumn) + 1)
Next

Thank you!

[ddanbe]

To loop through individual excel cells, use the Cells property.
See here how to adress excel cells.

[JamesCherrill]

Why a loop?
Why not just create a sum formula with one relative bound and one absolute, eg
cell f1 =sum(f1:$O1)
and replicate that by dragging it across the remaining columns.