Variable inside formula

predzer

New Member
Joined
Jun 21, 2018
Messages
20
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.
 

Some videos you may like

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
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 21, 2018
Messages
20
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 :)
 

predzer

New Member
Joined
Jun 21, 2018
Messages
20

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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.

In your function
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
Joined
Jun 21, 2018
Messages
20

ADVERTISEMENT

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.

In your function
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
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 21, 2018
Messages
20
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 :biggrin: Thanks so much!

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

Watch MrExcel Video

Forum statistics

Threads
1,108,621
Messages
5,523,947
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top