Formula Advice

[smithb2]

Within an excel worksheet, I am keeping track of the hours I work per day in a week.

I only work part-time right now so the number of columns I have in the worksheet vary. I would like to
write a macro that will determine the # of columns and include this info in a macro inserted sum Formula
at the bottom of the list of hours.

I have determined the # of columns via a Do loop and stored this in an int variable, however, I have not been able to get the sum formula to accept a value via a variable resulting in a run-time error,
application-defined or object-defined error.

Thank you very much for the help. It is much appreciated.

Sincerely,
Smithb2

 

[smithb2]

Solution Found

By help of the group, I have found a solution.

As you may recall, as with your situation, I was trying to automatically determine, with a macro, how many columns were to be added and insert this info into the sum formula:

Option Explicit
Dim i As Integer

Sub HrsTotal()
i = 0

‘Do loop determines # of columns to store in integer variable (Hrs is the header for the ‘column

Do While ActiveCell.Value <> "Hrs"

ActiveCell.Offset(-1, 0).Select

i = i + 1

Loop

‘Following line uses the # stored in “i” to select cell in which to place sum function

ActiveCell.Offset(Int(i), 0).Select

‘In the following, I simply turn the value stored in “i” from positive to negative, in ‘preparation for sum formula.

i = -(i)

ActiveCell.FormulaR1C1 = "=SUM(R[" & i & "]C:R[-1]C)"

End Sub


The last line contains what I was unable to figure out, How to get the sum formula to accept the variable "i" as a value: " & i & "


Thank you.

Have a great day,
Smithb2