Help! Need formula to work right with variable

[sagain2k]

I have a formula that works fine like this:

ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-1])"

But I now want to be able to use a variable called "calcalnum" to replace the "4" in the above formula. Can't quite get the syntax to work without errors... I'd like to be able to use any function in a formula and use variables to represent the offset numbers.

Getting pointed to the correct syntax for this would be appreciated! Thanks!

 

[Tom Urtis]

Try this. The error trap is for when no value, or a non-numeric value, exists in your range of interest.

Sub Calc()
Dim calcalnum As Range
Set calcalnum = ActiveCell.Offset(0, 4)
On Error Resume Next
ActiveCell.Value = Application.WorksheetFunction.Average(calcalnum, calcalnum.Offset(0, 1))
End Sub