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


--
HTHs Martin Fishlock


"John Smith" wrote:

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.