Passing arguments to my offset formula
Author Message
Passing arguments to my offset formula

The following formula is very useful for creating quarterly data from
monthly. Can be modified to do monthly to yearly etc.
What I would like is to use user defined base or starting point (ie \$B
\$3) and frequency values (the 3 is for monthly to quarterly, 12 for
monthly to yearly).
I have tried the obvious things I can find in books but am not having
any luck with this.
Any ideas.
Thanks
Sub MtoQ()

ActiveCell.Formula = "=SUM(OFFSET(\$B\$3,0,(COLUMN(B3)-2)*3)_
:OFFSET(\$B\$3,0,(COLUMN(B3)-2)*3+2))"

End Sub
Dom

Fri, 25 Nov 2005 07:01:39 GMT
Passing arguments to my offset formula
Dominic,

I think that this is what you want, I have changed it to a function
returning the formula

Function MtoQ(start As Range, period As Integer)
MtoQ = "=SUM(OFFSET(" & start.Address & ",0,(COLUMN(" & start.Address &
")-2)*3):" & _
"OFFSET(" & start.Address & ",0,(COLUMN(" & start.Address &
")-2)*3+(" & period & "-1)))"
End Function

You can call it from your macro like this
ActiveCell.Formula = MtoQ(Range("B3"), 12)

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

Quote:
> The following formula is very useful for creating quarterly data from
> monthly. Can be modified to do monthly to yearly etc.
> What I would like is to use user defined base or starting point (ie \$B
> \$3) and frequency values (the 3 is for monthly to quarterly, 12 for
> monthly to yearly).
> I have tried the obvious things I can find in books but am not having
> any luck with this.
> Any ideas.
> Thanks
> Sub MtoQ()

> ActiveCell.Formula = "=SUM(OFFSET(\$B\$3,0,(COLUMN(B3)-2)*3)_
> :OFFSET(\$B\$3,0,(COLUMN(B3)-2)*3+2))"

> End Sub
> Dom

Fri, 25 Nov 2005 16:47:42 GMT
Passing arguments to my offset formula

Quote:
> Function MtoQ(start As Range, period As Integer)
>     MtoQ = "=SUM(OFFSET(" & start.Address & ",0,(COLUMN(" & start.Address &
> ")-2)*3):" & _
>             "OFFSET(" & start.Address & ",0,(COLUMN(" & start.Address &
> ")-2)*3+(" & period & "-1)))"
> End Function

> You can call it from your macro like this
>     ActiveCell.Formula = MtoQ(Range("B3"), 12)

Thanks Bob.
A bit more tweaking and I should have this up and going. What I intend
is to make the formula more user friendly by simplifying changes. That
way users can simply input their start, frequency, etc and then be able
to copy the formula across or down to create the new series.
Thanks again for the help
Dom

Sat, 26 Nov 2005 12:18:11 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages