Macro - define cell range for a sum function

[Guest]

Hello, I'm trying to sum certain cells in macro, however, the cell range is
not fixed;(i.e cells are based on variables)

Herebelow is an example of the macro but it's giving an error at the sum
funtion (would appreciate if you could help me find the mistake):

Sub R()
Dim i As Integer, x As Integer, y As Integer, z As Integer

For i = 2 To 15
y = i - z
x = i - 1
Cells(i, "H").Formula = "=Sum(Cells(y, "H"), Cells(x, "H"))"
z = 0
y = 0
x = 0
Next i
End Sub

Thank you

 

[Dave Peterson]

How about:

Option Explicit

Sub R()

Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim rng As Range

For i = 2 To 15
y = i - z
x = i - 1
Set rng = Range(Cells(y, "H"), Cells(x, "H"))
Cells(i, "H").Formula = "=Sum(" & rng.Address(0, 0) & ")"
z = 0
y = 0
x = 0
Next i
End Sub

But you don't need y = 0, x = 0 at the bottom. Those variables get set in the
top statements and wouldn't care if it was set to 0 or not.

 

[Guest]

Your logic is convoluted, at best. In this code, 'z' is ALWAYS zero, so the
line:

y = i - z

does nothing more than set y equal to i. Why not just use i? Also, there's
no need to set x, y, and z to zero before the Next i command.

Finally, it looks like you are writing a bunch of circular formulas, since y
= i. You are trying to make the formula in say, H2, equal the sum of H2 & H1

Change the column for the formula to something else, say "A" and the command
should be

range("A2:A15").Formula = "=sum(h1:h2)"

or, if you want to use variables

range(cells(i,"A"),cells(x,"A")).formula = "=sum(H" & i & ":H" & i-1 & ")"

where i and x equal the lower & upper end of your For..Next variable