| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells with dynamic range
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. Stefi wrote: 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). "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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula on a dynamic range of cells | Excel Discussion (Misc queries) | |||
| Hlp! Protect Cells in Dynamic Range | Excel Discussion (Misc queries) | |||
| count blank cells in a dynamic range | Excel Programming | |||
| Dynamic Named Range with blank cells | Excel Discussion (Misc queries) | |||
| Selecting and copying a dynamic range of cells | Excel Programming | |||
Hybrid Mode
