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


"Stefi" wrote:


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.