sum of cells with dynamic range

[John Smith]

I need to sum up the values in a series of cells in a column. The
problem is the cells are generated during run-time, depending on
user input. How to refer to cells with a dynamic range?

What I need is scripts that do the following, which do not work.

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

The problem in this one is vba doesn't recognize sum().

2. Sheet1.Cells(9, 9).formula =
"=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

The problem here is the worksheet sheet1 doesn't recognize
userform MC. Even if I can store the textbox value in a cell in
sheet1, I still cannot use something like R[R4C2]C[-4] in the
formula. Thanks for any help.

 

[Guest]

John,

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4), Cells(MC.textbox1.Value, -4))

You need to use the qualifier application.worksheetfunction as:

Sheet1.Cells(9, 8).Value = _ &
Application.Worksheetfunction.Sum(Cells(14, 4), Cells(MC.textbox1.Value, -4))


Also the -4 appears to be work as the cells refers to actual cells starting
at 0,0 for A1.

2. Sheet1.Cells(9, 9).formula = "=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

You need to take the MC.textbox1.value out of the quotes:

Sheet1.Cells(9, 9).formula = "=SUM(R[5]C[-4]:R[" & MC.textbox1.value &
"]C[-4])"

 

[Guest]

Hi John,

Try this:

1. Sheet1.Cells(9, 8).Value = Worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

Regards,
Stefi

„John Smith†ezt írta:

I need to sum up the values in a series of cells in a column. The
problem is the cells are generated during run-time, depending on
user input. How to refer to cells with a dynamic range?

What I need is scripts that do the following, which do not work.

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

The problem in this one is vba doesn't recognize sum().

2. Sheet1.Cells(9, 9).formula =
"=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

The problem here is the worksheet sheet1 doesn't recognize
userform MC. Even if I can store the textbox value in a cell in
sheet1, I still cannot use something like R[R4C2]C[-4] in the
formula. Thanks for any help.

 

[Guest]

John,

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4), Cells(MC.textbox1.Value, -4))

You need to qualify the sum function as it is a worksheet function:

Sheet1.Cells(9, 8).Value = application.worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

The -4 seems to be incorrect too. as the cells function refers to absolute
references and A1 is 0,0.

2. Sheet1.Cells(9, 9).formula = _
"=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

You need to move the mc... reference outside the quotes:

Sheet1.Cells(9, 9).formula = "=SUM(R[5]C[-4]:R[ " & _
MC.textbox1.value & "]C[-4])"

 

[Bob Phillips]

Sheet1.Cells(9, 9).formula = "=SUM(R[5]C[-4]:R[" & MC.textbox1.value &
"]C[-4])"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

I think he may also need to change Cells(MC.textbox1.Value, -4). There is no
column number -4 (unless the reference is relative to another range).

Hi John,

Try this:

1. Sheet1.Cells(9, 8).Value = Worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

Regards,
Stefi

„John Smith†ezt írta:

I need to sum up the values in a series of cells in a column. The
problem is the cells are generated during run-time, depending on
user input. How to refer to cells with a dynamic range?

What I need is scripts that do the following, which do not work.

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

The problem in this one is vba doesn't recognize sum().

2. Sheet1.Cells(9, 9).formula =
"=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

The problem here is the worksheet sheet1 doesn't recognize
userform MC. Even if I can store the textbox value in a cell in
sheet1, I still cannot use something like R[R4C2]C[-4] in the
formula. Thanks for any help.

 

[John Smith]

After correcting all the typos, I still can't make it to work.
Now, Excel stores the value of Cells(14,4) to Cells(9,8) but what
I want is sum(D14(textbox1.value)).

I tried using "worksheetfunction.sum(cells(14.4):cells(...)).
Before I could finish typing, Excel complained about the colon,

I think he may also need to change Cells(MC.textbox1.Value, -4). There is no
column number -4 (unless the reference is relative to another range).


:

Hi John,

Try this:

1. Sheet1.Cells(9, 8).Value = Worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))
Regards,
Stefi



I need to sum up the values in a series of cells in a column. The
problem is the cells are generated during run-time, depending on
user input. How to refer to cells with a dynamic range?

What I need is scripts that do the following, which do not work.

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

The problem in this one is vba doesn't recognize sum().

2. Sheet1.Cells(9, 9).formula =
"=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

The problem here is the worksheet sheet1 doesn't recognize
userform MC. Even if I can store the textbox value in a cell in
sheet1, I still cannot use something like R[R4C2]C[-4] in the
formula. Thanks for any help.

 

[Guest]

You can choose from these solutions:

Range("H9").Value = WorksheetFunction.Sum(Range("D14" & textbox1.Value))

Range("H9").FormulaR1C1 = "=SUM(R[5]C[-4]:R[" & textbox1.Value - 9 & "]C[-4])"

Cells(9, 8).Value = WorksheetFunction.Sum(Range("D14" & textbox1.Value))

Cells(9, 8).FormulaR1C1 = "=SUM(R[5]C[-4]:R[" & textbox1.Value - 9 & "]C[-4])"

Cells(9, 8).Value = WorksheetFunction.Sum(Range(Cells(9, 4),
Cells(textbox1.Value, 4)))

Regards,
Stefi


„John Smith†ezt írta:

After correcting all the typos, I still can't make it to work.
Now, Excel stores the value of Cells(14,4) to Cells(9,8) but what
I want is sum(D14(textbox1.value)).

I tried using "worksheetfunction.sum(cells(14.4):cells(...)).
Before I could finish typing, Excel complained about the colon,

I think he may also need to change Cells(MC.textbox1.Value, -4). There is no
column number -4 (unless the reference is relative to another range).


:

Hi John,

Try this:

1. Sheet1.Cells(9, 8).Value = Worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

Regards,
Stefi



I need to sum up the values in a series of cells in a column. The
problem is the cells are generated during run-time, depending on
user input. How to refer to cells with a dynamic range?

What I need is scripts that do the following, which do not work.

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

The problem in this one is vba doesn't recognize sum().

2. Sheet1.Cells(9, 9).formula =
"=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

The problem here is the worksheet sheet1 doesn't recognize
userform MC. Even if I can store the textbox value in a cell in
sheet1, I still cannot use something like R[R4C2]C[-4] in the
formula. Thanks for any help.

 

[John Smith]

Wow! VBA is certainly more powerful than I expected. I tested the
fourth one and it works like a charm. Thanks a lot.

You can choose from these solutions:

Range("H9").Value = WorksheetFunction.Sum(Range("D14" & textbox1.Value))

Range("H9").FormulaR1C1 = "=SUM(R[5]C[-4]:R[" & textbox1.Value - 9 & "]C[-4])"

Cells(9, 8).Value = WorksheetFunction.Sum(Range("D14" & textbox1.Value))

Cells(9, 8).FormulaR1C1 = "=SUM(R[5]C[-4]:R[" & textbox1.Value - 9 & "]C[-4])"

Cells(9, 8).Value = WorksheetFunction.Sum(Range(Cells(9, 4),
Cells(textbox1.Value, 4)))

Regards,
Stefi

After correcting all the typos, I still can't make it to work.
Now, Excel stores the value of Cells(14,4) to Cells(9,8) but what
I want is sum(D14(textbox1.value)).

I tried using "worksheetfunction.sum(cells(14.4):cells(...)).
Before I could finish typing, Excel complained about the colon,

I think he may also need to change Cells(MC.textbox1.Value, -4). There is no
column number -4 (unless the reference is relative to another range).


:



Hi John,

Try this:


1. Sheet1.Cells(9, 8).Value = Worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

Regards,
Stefi




I need to sum up the values in a series of cells in a column. The
problem is the cells are generated during run-time, depending on
user input. How to refer to cells with a dynamic range?

What I need is scripts that do the following, which do not work.

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

The problem in this one is vba doesn't recognize sum().

2. Sheet1.Cells(9, 9).formula =
"=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

The problem here is the worksheet sheet1 doesn't recognize
userform MC. Even if I can store the textbox value in a cell in
sheet1, I still cannot use something like R[R4C2]C[-4] in the
formula. Thanks for any help.

 

[Guest]

You are welcome! Thanks for the feedback!
Stefi

„John Smith†ezt írta:

Wow! VBA is certainly more powerful than I expected. I tested the
fourth one and it works like a charm. Thanks a lot.

You can choose from these solutions:

Range("H9").Value = WorksheetFunction.Sum(Range("D14" & textbox1.Value))

Range("H9").FormulaR1C1 = "=SUM(R[5]C[-4]:R[" & textbox1.Value - 9 & "]C[-4])"

Cells(9, 8).Value = WorksheetFunction.Sum(Range("D14" & textbox1.Value))

Cells(9, 8).FormulaR1C1 = "=SUM(R[5]C[-4]:R[" & textbox1.Value - 9 & "]C[-4])"

Cells(9, 8).Value = WorksheetFunction.Sum(Range(Cells(9, 4),
Cells(textbox1.Value, 4)))

Regards,
Stefi

After correcting all the typos, I still can't make it to work.
Now, Excel stores the value of Cells(14,4) to Cells(9,8) but what
I want is sum(D14(textbox1.value)).

I tried using "worksheetfunction.sum(cells(14.4):cells(...)).
Before I could finish typing, Excel complained about the colon,


JMB wrote:

I think he may also need to change Cells(MC.textbox1.Value, -4). There is no
column number -4 (unless the reference is relative to another range).


:



Hi John,

Try this:


1. Sheet1.Cells(9, 8).Value = Worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

Regards,
Stefi




I need to sum up the values in a series of cells in a column. The
problem is the cells are generated during run-time, depending on
user input. How to refer to cells with a dynamic range?

What I need is scripts that do the following, which do not work.

1. Sheet1.Cells(9, 8).Value = Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))

The problem in this one is vba doesn't recognize sum().

2. Sheet1.Cells(9, 9).formula =
"=SUM(R[5]C[-4]:R[MC.textbox1.value]C[-4])"

The problem here is the worksheet sheet1 doesn't recognize
userform MC. Even if I can store the textbox value in a cell in
sheet1, I still cannot use something like R[R4C2]C[-4] in the
formula. Thanks for any help.