Creating a macro that uses a function with a variable

Question

Heres my code:

Dim N As Single
N = ActiveCell.Offset(0, 0).Value
E = 1 - N

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=-SUM(R[E]C:R[-1]C)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=-SUM(R[E]C:R[-1]C)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=-SUM(R[E]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select

I want the macro user to select a cell with a value (example 5) as it will be a slot for the 5th row of values. I want the program to find the negative sum of all the values prior to it (1,2,3 and 4).

I think I can answer this question with a much better solution then you seem to be trying for, but can you please be more clear on the data you have and what you want to do with it? — user2140261, Apr 03 '13 at 21:43

score 0 · Answer 1 · edited May 23 '17 at 12:13

0

Though I am not in favor of .Select. See this.

But is this what you are trying? E is a variable. Inside the double quotes it behaves as a string :)

Sub Sample()
    Dim N As Single
    N = ActiveCell.Offset(0, 0).Value
    E = 1 - N

    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=-SUM(R[" & E & "]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=-SUM(R[" & E & "]C:R[-1]C)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=-SUM(R[" & E & "]C:R[-1]C)"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

edited May 23 '17 at 12:13

Community

1
1

answered Apr 03 '13 at 20:49

Siddharth Rout

147,039
17
206
250

1

It is possible that this is what the OP asked for - but is this just encouraging ugly code? – Floris Apr 03 '13 at 23:50
No I am not encouraging ugly code ;) Please see the first line of my post. My post focuses primarily on how to use a variable with a string :) – Siddharth Rout Apr 04 '13 at 05:40
Fair enough about the first line! – Floris Apr 04 '13 at 10:42

Our Man in Bananas · Answer 2 · 2013-04-04T16:01:34.273

0

Might I suggest the below as an alternative avoiding the use of SELECT?

Sub SampleWithoutSelect()
Dim N As Single
N = ActiveCell.Offset(0, 0).Value
E = 1 - N

for iIndx=0 to -3 step -1
    range(activecell.offset(iindx,0).formular1c1="=-SUM(R[" & E & "]C:R[-1]C)"
next

End Sub

is that what you want - or am I missing something?

edited Apr 04 '13 at 16:01

answered Apr 04 '13 at 11:32

Our Man in Bananas

5,809
21
91
148

Creating a macro that uses a function with a variable

2 Answers2