Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro - define cell range for a sum function
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 |
#2
|
|||
|
|||
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. On the other hand, I have no idea what z is used for. Fad wrote: 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 |
#3
|
|||
|
|||
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 "Fad" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Function to remove a space from text in cell WITHOUT macro?? | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions | |||
Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions |