The nicest way to put formulas like this into cells is to use R1C1
addressing mode.
This works because you want to keep the sum on the same row as the formula -
wherever that is - and R1C1 does this neatly. There are two alternatives.
Absolute:
xx.FormulaR1C1 = "=sum(RC4:RC7)"
(where xx is the H cell you want the formula in). Sums columns 4 to 7 in
the current row. Or relative:
xx.FormulaR1C1 = "=sum(RC[-4]:RC[-1])"
Sums the columns 4 to the left through to 1 to the left. When put into an H
cell, both will do the same thing.
The good thing about the R1C1 way of doing it is that the formula doesn't
need to be reworked to match the row you're putting it on. For instance, I
attached this to a command button
Option Explicit
Private Sub CommandButton1_Click()
Range("h3").FormulaR1C1 = "=sum(RC[-4]:RC[-1])"
Range("h8").FormulaR1C1 = "=sum(RC[-4]:RC[-1])"
Range("h10").FormulaR1C1 = "=sum(RC4:RC7)"
Range("h15").FormulaR1C1 = "=sum(RC4:RC7)"
End Sub
Note that the formulas are the same no matter which row they're going in.
(The ones for 3 and 8 are the relative form, and the other two are the
absolute form. In practice, you'd choose one form and use it for all the
formulas.)