# Variable inside formula

#### predzer

##### New Member
Hi all,

I am trying to insert a variable into a formula. its not returning any error but it selects 5 columns before col value.

Code:
``````col = .column
Selection.FormulaR1C1 = "=AVERAGEIF(Date,R9C,RC[-" & col  & "]:RC[-2])"``````

Without variable the code is, (active cell column number is 227)

Code:
``Selection.FormulaR1C1 = "=AVERAGEIF(Date,R9C,RC116:RC225)``

col returns 116 but range selected is 111.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Peter_SSs

##### MrExcel MVP, Moderator
Hi all,

I am trying to insert a variable into a formula. its not returning any error but it selects 5 columns before col value.

Code:
``````col = .column
Selection.FormulaR1C1 = "=AVERAGEIF(Date,R9C,RC[COLOR="#FF0000"][B][-[/B][/COLOR]" & col  & "[COLOR="#FF0000"][B]][/B][/COLOR]:RC[-2])"``````

Without variable the code is, (active cell column number is 227)

Code:
``Selection.FormulaR1C1 = "=AVERAGEIF(Date,R9C,RC116:RC225)``

col returns 116 but range selected is 111.
That is because 227 - 116 = 111

If col =116 & you want RC116 in your formula, then you wouldn't have the red parts marked above.

What is Date in your formula?

• predzer

#### predzer

##### New Member
That is because 227 - 116 = 111

If col =116 & you want RC116 in your formula, then you wouldn't have the red parts marked above.

What is Date in your formula?

Date is a named range.

I tested without the brackets, and it works. Thanks for your time #### Peter_SSs

##### MrExcel MVP, Moderator
I tested without the brackets, and it works. Thanks for your time Glad to hear it. You're welcome. #### predzer

##### New Member

That is because 227 - 116 = 111

If col =116 & you want RC116 in your formula, then you wouldn't have the red parts marked above.

What is Date in your formula?

I am again stuck here, since i have defined 'date' as named range i cannot loop it for rest of the sheets. so i tried to replace date as a variable using below code. But its not working.

Code:
``````Dim date As Range
Set date = Range(ref, ref.End(xlToRight))  'ref is another range, this line of code works perfectly

Selection.FormulaR1C1 = "=AVERAGEIF(" & date & ",R9C,RC" & cp & ":RC" & cd & ")"``````

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
Date is a function in vba so I would highly recommend not using it as a variable name. Let's assume you use rDate instead.

Rich (BB code):
``Selection.FormulaR1C1 = "=AVERAGEIF(" & rDate.Address & ",R9C,RC" & cp & ":RC" & cd & ")"``

The other thing that could go wrong, and I have no way to check with that limited code and no data, is that rDate and the last range in the function defined by RC" & cp & ":RC" & cd must be the same size.

#### predzer

##### New Member

Date is a function in vba so I would highly recommend not using it as a variable name. Let's assume you use rDate instead.

Rich (BB code):
``Selection.FormulaR1C1 = "=AVERAGEIF(" & rDate.Address & ",R9C,RC" & cp & ":RC" & cd & ")"``

The other thing that could go wrong, and I have no way to check with that limited code and no data, is that rDate and the last range in the function defined by RC" & cp & ":RC" & cd must be the same size.

Below is a snippet of my code which i have used. (Getting Application Defined Error)

Code:
``````Sub Test()

Dim T1 As Range
Dim T1End As Range
Dim rdate As Range

Set T1 = Range("E8").End(xlToRight).Offset(0, 2)
Set T1End = T1.End(xlToRight)
Set rdate = Range(T1, T1.End(xlToRight))

With T1
cp = .Column
End With

With T1End
cd = .Column
End With

Selection.FormulaR1C1 = "=AVERAGEIF(" & rdate.address & ",R9C,RC" & cp & ":RC" & cd & ")"

End Sub``````
I will be knowing only cell E8. Rest all are dynamic in nature so i have used end functions to determine cell address.

#### Peter_SSs

##### MrExcel MVP, Moderator
Below is a snippet of my code which i have used. (Getting Application Defined Error)
You haven't said what line is giving the error but it could easily be the formula line due to my oversight. It should be
Rich (BB code):
``Selection.FormulaR1C1 = "=AVERAGEIF(" & rdate.Address(ReferenceStyle:=xlR1C1) & ",R9C,RC" & cp & ":RC" & cd & ")"``

If that is not it, the following line would give that error if there is no data on row 8 to the right of E8 on the active sheet.
Rich (BB code):
``Set T1 = Range("E8").End(xlToRight).Offset(0, 2)``

In any case, you should carefully check the resulting formula once the error(s) are eliminated as it looks like there could be some problems with it. However, once again I am guessing as I am not looking at your data.

• predzer

#### predzer

##### New Member
You haven't said what line is giving the error but it could easily be the formula line due to my oversight. It should be
Rich (BB code):
``Selection.FormulaR1C1 = "=AVERAGEIF(" & rdate.Address(ReferenceStyle:=xlR1C1) & ",R9C,RC" & cp & ":RC" & cd & ")"``

If that is not it, the following line would give that error if there is no data on row 8 to the right of E8 on the active sheet.
Rich (BB code):
``Set T1 = Range("E8").End(xlToRight).Offset(0, 2)``

In any case, you should carefully check the resulting formula once the error(s) are eliminated as it looks like there could be some problems with it. However, once again I am guessing as I am not looking at your data.

(ReferenceStyle:=xlR1C1) Did the trick Thanks so much!

(Btw the formula line of code was giving error, now its sorted out )

Replies
4
Views
52
Replies
3
Views
46
Replies
6
Views
64
Replies
3
Views
46
Replies
1
Views
75