Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula construction
I have the following code:
<BEGIN VBA CODE Dim startRng, endRng As Range With Sheets("sheet2") Set startRng = .Range(.Range("b2"), .Range("b100")) Set endRng = .Range(.Range("b2"), .Range("b100")) End With Set startRng = startRng.Find("Americold", , xlValues, xlWhole).Offset(2, 0) Set endRng = endRng.Find("Totals", , xlValues, xlWhole).Offset(-1, 0) endRng.Offset(1, 2).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE i need to construct a formula (=sum(startRng:endRng)) that will sum the values from startRng to endRng i am using the following line to do so: <BEGIN VBA CODE endRng.Offset(1, 2).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE when i run my code the cell contains the formula <BEGIN FORMULA =SUM('FC112':'FC112') <END FORMULA which, in effect is correct based on the code. the cell referred to by startRng contains the value FC112 the cell referred to by endRng (before re-assignment) also contains FC112 the cell referred to by endRng.Offset(1,2) is empty However - when i change the code to <BEGIN VBA CODE Dim startRng, endRng As Range With Sheets("sheet2") Set startRng = .Range(.Range("b2"), .Range("b100")) Set endRng = .Range(.Range("b2"), .Range("b100")) End With Set startRng = startRng.Find("Americold", , xlValues, xlWhole).Offset(2, 2) Set endRng = endRng.Find("Totals", , xlValues, xlWhole).Offset(-1, 2) endRng.Offset(1, 0).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE I get a Run-time error '1004': Application-defined or object defined error. in this case the cell referred to by startRng contains the value 0 (via formula =V4, V4 contains 0) the cell referred to by endRng (before re-assignment) contains the value 0 (via formula =V4, V4 contains 0) the cell referred to by endRng.Offset(1,0) is empty what i need to do is construct a formula to sum startRng:endRng any thoughts or suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula construction
endRng.Offset(1, 2).Formula = "=sum(" & startRng.Address(0,0) _
& ":" & endRng.Address(0,0) & ")" Notice I am using Formula rather than FormulaR1C1 also, rather than variant and range, why not declare both variables as Range Dim startRng as Range, endRng As Range -- Regards, Tom Ogilvy "Gixxer_J_97" wrote in message ... I have the following code: <BEGIN VBA CODE Dim startRng, endRng As Range With Sheets("sheet2") Set startRng = .Range(.Range("b2"), .Range("b100")) Set endRng = .Range(.Range("b2"), .Range("b100")) End With Set startRng = startRng.Find("Americold", , xlValues, xlWhole).Offset(2, 0) Set endRng = endRng.Find("Totals", , xlValues, xlWhole).Offset(-1, 0) endRng.Offset(1, 2).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE i need to construct a formula (=sum(startRng:endRng)) that will sum the values from startRng to endRng i am using the following line to do so: <BEGIN VBA CODE endRng.Offset(1, 2).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE when i run my code the cell contains the formula <BEGIN FORMULA =SUM('FC112':'FC112') <END FORMULA which, in effect is correct based on the code. the cell referred to by startRng contains the value FC112 the cell referred to by endRng (before re-assignment) also contains FC112 the cell referred to by endRng.Offset(1,2) is empty However - when i change the code to <BEGIN VBA CODE Dim startRng, endRng As Range With Sheets("sheet2") Set startRng = .Range(.Range("b2"), .Range("b100")) Set endRng = .Range(.Range("b2"), .Range("b100")) End With Set startRng = startRng.Find("Americold", , xlValues, xlWhole).Offset(2, 2) Set endRng = endRng.Find("Totals", , xlValues, xlWhole).Offset(-1, 2) endRng.Offset(1, 0).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE I get a Run-time error '1004': Application-defined or object defined error. in this case the cell referred to by startRng contains the value 0 (via formula =V4, V4 contains 0) the cell referred to by endRng (before re-assignment) contains the value 0 (via formula =V4, V4 contains 0) the cell referred to by endRng.Offset(1,0) is empty what i need to do is construct a formula to sum startRng:endRng any thoughts or suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula construction
endRng.Offset(1, 0).FormulaR1C1 = "=sum(" & startRng.Address & ":" &
endRng.Address & ")" -- HTH RP (remove nothere from the email address if mailing direct) "Gixxer_J_97" wrote in message ... I have the following code: <BEGIN VBA CODE Dim startRng, endRng As Range With Sheets("sheet2") Set startRng = .Range(.Range("b2"), .Range("b100")) Set endRng = .Range(.Range("b2"), .Range("b100")) End With Set startRng = startRng.Find("Americold", , xlValues, xlWhole).Offset(2, 0) Set endRng = endRng.Find("Totals", , xlValues, xlWhole).Offset(-1, 0) endRng.Offset(1, 2).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE i need to construct a formula (=sum(startRng:endRng)) that will sum the values from startRng to endRng i am using the following line to do so: <BEGIN VBA CODE endRng.Offset(1, 2).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE when i run my code the cell contains the formula <BEGIN FORMULA =SUM('FC112':'FC112') <END FORMULA which, in effect is correct based on the code. the cell referred to by startRng contains the value FC112 the cell referred to by endRng (before re-assignment) also contains FC112 the cell referred to by endRng.Offset(1,2) is empty However - when i change the code to <BEGIN VBA CODE Dim startRng, endRng As Range With Sheets("sheet2") Set startRng = .Range(.Range("b2"), .Range("b100")) Set endRng = .Range(.Range("b2"), .Range("b100")) End With Set startRng = startRng.Find("Americold", , xlValues, xlWhole).Offset(2, 2) Set endRng = endRng.Find("Totals", , xlValues, xlWhole).Offset(-1, 2) endRng.Offset(1, 0).FormulaR1C1 = "=sum(" & startRng & ":" & endRng & ")" <END VBA CODE I get a Run-time error '1004': Application-defined or object defined error. in this case the cell referred to by startRng contains the value 0 (via formula =V4, V4 contains 0) the cell referred to by endRng (before re-assignment) contains the value 0 (via formula =V4, V4 contains 0) the cell referred to by endRng.Offset(1,0) is empty what i need to do is construct a formula to sum startRng:endRng any thoughts or suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am looking for formula to calculate escalation on construction | Excel Discussion (Misc queries) | |||
formula construction | New Users to Excel | |||
Efficient Array Formula Construction | Excel Discussion (Misc queries) | |||
formula construction | Excel Discussion (Misc queries) | |||
Construction Loan Interest Formula | Excel Worksheet Functions |