Variable inside formula

[predzer]

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.

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

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

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

col returns 116 but range selected is 111.

 

[Peter_SSs]

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.

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)

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]

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]

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

Glad to hear it. You're welcome.

 

[predzer]

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.

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 & ")"

 

[Peter_SSs]

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

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]

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

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)

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]

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

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.

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.

 

[Fluff]

@predzer
Is this a continuation of this thread?
https://www.mrexcel.com/forum/excel...range-address-unavailable-inside-formula.html

 

[predzer]

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

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.

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 )