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  
 
 [ 3 post ] 

 Relevant Pages 

1. OFFSET with array 2nd or 3rd argument

2. Offset argument

3. OFFSET with array 2nd or 3rd argument

4. Pass an argument to an Access query with sql

5. Offsetting ranges in a formula...

6. Offset Formula

7. Offset formula

8. Passing argument in printing headers - title

9. passing many arguments to a function in Excel

10. Passing an entire column as an argument in a function


 
Powered by phpBB® Forum Software © phpBB Group