How to make a Formula Dynamic

[sourabh_ajmera]

Hello everybody,

How do i make this fomula dynamic? It is selecting a range of columns
The number of end columns keep on changing (increases daily)
i.e. "=IFERROR(AVERAGEIFS(RC11:RC172....here 172 will change to some other column but C11 will be constant


Formula:
ActiveCell.FormulaR1C1 = _
"=IFERROR(AVERAGEIFS(RC11:RC172,titlesw,"">=""&MONTH(R1C)&""/1/""&YEAR(R1C),titlesw,""<=""&MONTH(R1C)&""/""&DAY(DATE(YEAR(R1C),MONTH(R1C)+1,0))&""/""&YEAR(R1C)),0)"

Appreciate your help! Thank you in advance

 

[blbat]

Looks like you want to find Last Column.

read this thread with Jonmo1's replies:

http://www.mrexcel.com/forum/excel-questions/786503-finding-last-column.html

 

[sourabh_ajmera]

I have already found out the last column. This is a column in between like 14 columns down the last column. So I want to know how to use that in here.

I tried something like.

1.Found the last column (say x)
2.Formula:
ActiveCell.FormulaR1C1 = _
"=IFERROR(AVERAGEIFS(RC11:RC(x-14),titlesw,"">=""&MONTH(R1C)&""/1/""&YEAR(R1C),titlesw,""<=""&MONTH(R1C)&""/""&DAY(DATE(YEAR(R1C),MONTH(R1C)+1,0))&""/""&YEAR(R1C)),0)"

which does not work.

Is there any other way?
Please help. I am a beginner in vba..

Thanks

 

[sourabh_ajmera]

I want to make the formula dynamic so that it accomodates the changing columns

My efforts to make it dynamic went in vain. Can somebody please help me on this??

Step 1: Found the last column (say x)
Step 2: Formula:
ActiveCell.FormulaR1C1 = _
"=IFERROR(AVERAGEIFS(RC11:RC(x-14),titlesw,"">=""&MONTH(R1C)&""/1/""&YEAR(R1C),titlesw,""<=""&MONTH(R1C)&""/""&DAY(DATE(YEAR(R1C),MONTH(R1C)+1,0))&""/""&YEAR(R1C)),0)"

which does not work.

The last column of the range keeps on changing. I want to know if there any way in which I can use that column name/no in the formula to make it dynamic

Please help. I am a beginner in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>..

 

[blbat]

if you want to do this in a formula, you're going to need some named ranges.

is the last row always the same in all the columns?

 

[blbat]

if so, you can create a named range that find your last row

Name: LastRow
Scope: Workbook
refers to: =MATCH(REPT("Z",30),yourworksheetname!$A:$A)

then for a given Column, lets say Column "M" for example, you can create another named range using "LastRow":

Name: ColM
Scope: Workbook
refers to: =yourworksheetname!$M$2:INDEX(yourworksheetname!$M:$M,yourworkbookname.xlsx!LastRow)

Then you can use ColM instead of an explicit range.


I've not created a Named Range for Last Column before...so will work on that, meanwhile I hope one of the big guns drops in to help.

 

[Jonmo1]

Try

"=IFERROR(AVERAGEIFS(RC11:RC" & x-14 & ",titlesw,"">=""&........

 

[sourabh_ajmera]

Thank you @blbat and @Jonmo1

@Jonmo1...it was very stupid of me to not see those quotes previously....well i think as a learner these mistakes are prone to happen...Thanks alot

 

[Jonmo1]

You're welcome.