formula construction

[Guest]

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?

 

[Tom Ogilvy]

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

 

[Bob Phillips]

endRng.Offset(1, 0).FormulaR1C1 = "=sum(" & startRng.Address & ":" &
endRng.Address & ")"


--

HTH

RP
(remove nothere from the email address if mailing direct)