Inserting value for offset?

[Lisa]

I'm attempting to use a value returned by a dcount in a sum routine. I recorded the sum macro and then attempted to replace the number of rows up offset with the dcount value preceded by a "-". I'm getting an error (application-defined or object-defined error).... here's the code:

Sub Macro2()

Dim i As Long
i = Sheets("information").Range("bcount").Value
Application.Goto Reference:="dr_91"
ActiveCell.FormulaR1C1 = "=SUM(R[-i]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub


Any suggestions would be greatly appreciated.
Thanks!
Lisa

[Jay Petrulis]

On 2002-05-01 08:18, Lisa wrote:
I'm attempting to use a value returned by a dcount in a sum routine. I recorded the sum macro and then attempted to replace the number of rows up offset with the dcount value preceded by a "-". I'm getting an error (application-defined or object-defined error).... here's the code:

Sub Macro2()

Dim i As Long
i = Sheets("information").Range("bcount").Value
Application.Goto Reference:="dr_91"
ActiveCell.FormulaR1C1 = "=SUM(R[-i]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub


Any suggestions would be greatly appreciated.
Thanks!
Lisa

Hi Lisa,

Change the line to read

ActiveCell.FormulaR1C1 = "=SUM(R[-" & i & "]C:R[-1]C)"

HTH,
Jay

[Lisa]

Thanks so much, Jay!

Works perfectly. So, it's necessary to insert text into functions like this, even when referring to a value? (Trying to get the concepts hammered home...)

Lisa

[Jay Petrulis]

On 2002-05-01 09:05, Lisa wrote:
Thanks so much, Jay!

Works perfectly. So, it's necessary to insert text into functions like this, even when referring to a value? (Trying to get the concepts hammered home...)

Lisa

Hi Lisa,

What you are doing in "building" the formula in the worksheet as if you were typing it in. That requires it to be entered as 'text' so your -i addition actually put '-i' in the formula on the worksheet.

Here, you have to separate the "text" you type on the worksheet from the value you want coded into the formula.

Hope this helps,
Jay