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!

Recommended Answers

All 4 Replies

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".

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)

Thank you!

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.