sum of cells with dynamic range

J

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.
 
M

Martin Fishlock

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])"
 
S

Stefi

Hi John,

Try this:
1. Sheet1.Cells(9, 8).Value = Worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))
Click to expand...

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.
Click to expand...
 
M

Martin Fishlock

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])"
 
B

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)
 
J

JMB

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).


Stefi said:
Hi John,

Try this:
1. Sheet1.Cells(9, 8).Value = Worksheetfunction.Sum(Cells(14, 4),
Cells(MC.textbox1.Value, -4))
Click to expand...

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.
Click to expand...
Click to expand...
 
J

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:D(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.
Click to expand...
Click to expand...
Click to expand...
 
S

Stefi

You can choose from these solutions:

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

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

Cells(9, 8).Value = WorksheetFunction.Sum(Range("D14:D" & 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:D(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.
Click to expand...
Click to expand...
Click to expand...
 
J

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:D" & textbox1.Value))

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

Cells(9, 8).Value = WorksheetFunction.Sum(Range("D14:D" & 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:D(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.
Click to expand...
Click to expand...
Click to expand...
 
S

Stefi

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:D" & textbox1.Value))

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

Cells(9, 8).Value = WorksheetFunction.Sum(Range("D14:D" & 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:D(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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with a VBA subroutine 0
Backwards compatibile macro 0
Range.Find problem 3
Select and sum all numbered cells in Column A 2
Macro to delete row if cells greater than certain value 1
Autofill a dynamic range with a formula. 1
Naming non-contigent cells with vba 5
fill in data that is not empty 1

Top