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.