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?
|