Help! Need formula to work right with variable

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
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!
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,501
Members
412,670
Latest member
Khin Zaw Htwe
Top