VBA to input a Cumulative formula

Good afternoon, all

I need to produce a VBA statement that will input a formula into a range of
cells in a Cumulative Hours column which is essentially "Sum everything from
the cell on the left up to the cell named "FirstHoursBooked" "

So, in G29, the formula would be =SUM(FirstHoursBooked:F29), in G30
=SUM(FirstHoursBooked:F30) and so on. This is nested in a loop which performs
the command a certain number of times, moving down a cell each time,
depending on whether the value in column F (Actual Hours) is greater than
zero, so, "No value in F, no Cumulative formula in G"

I'm having trouble coding this - I can do it in R1C1 notation thus:

For CumFormLoop = 1 To CumFormCount
ActiveCell.FormulaR1C1 = "=SUM(R29C6:RC[-1])"
ActiveCell.Offset(1, 0).Select
Next

But I want to use a range name instead of "R29", as if anyone alters the
structure of the worksheet, row 29 may no longer be the first cell in the
range to be summed.

I need some hybrid of R1C1 nottation that would look like this (although
this obviously doesn't work)

ActiveCell.FormulaR1C1 = "=SUM(FirstHoursBooked:RC[-1])"

Can anyone help - I demo this to the boss tomorrow!

Thanks in advance

Pete

iRow = Evaluate("MATCH(MIN(IF(B10:B25<>0,B10:B25)),B10:B25,0)")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Good afternoon, all
>
> I need to produce a VBA statement that will input a formula into a range
of
> cells in a Cumulative Hours column which is essentially "Sum everything
from
> the cell on the left up to the cell named "FirstHoursBooked" "
>
> So, in G29, the formula would be =SUM(FirstHoursBooked:F29), in G30
> =SUM(FirstHoursBooked:F30) and so on. This is nested in a loop which
performs
> the command a certain number of times, moving down a cell each time,
> depending on whether the value in column F (Actual Hours) is greater than
> zero, so, "No value in F, no Cumulative formula in G"
>
> I'm having trouble coding this - I can do it in R1C1 notation thus:
>
> For CumFormLoop = 1 To CumFormCount
> ActiveCell.FormulaR1C1 = "=SUM(R29C6:RC[-1])"
> ActiveCell.Offset(1, 0).Select
> Next
>
> But I want to use a range name instead of "R29", as if anyone alters the
> structure of the worksheet, row 29 may no longer be the first cell in the
> range to be summed.
>
> I need some hybrid of R1C1 nottation that would look like this (although
> this obviously doesn't work)
>
> ActiveCell.FormulaR1C1 = "=SUM(FirstHoursBooked:RC[-1])"
>
> Can anyone help - I demo this to the boss tomorrow!
>
> Thanks in advance
>
> Pete
>
>
>
>
>
>
>

Bob,

Thanks for this, although I must confess I don't quite understand it yet.
Only just replying to you as I was on half a day's leave yesterday.

I'll give it a go, anyway.

Regards

Pete



"Bob Phillips" wrote:

> iRow = Evaluate("MATCH(MIN(IF(B10:B25<>0,B10:B25)),B10:B25,0)")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Peter Rooney" <[email protected]> wrote in message
> news:[email protected]...
> > Good afternoon, all
> >
> > I need to produce a VBA statement that will input a formula into a range
> of
> > cells in a Cumulative Hours column which is essentially "Sum everything
> from
> > the cell on the left up to the cell named "FirstHoursBooked" "
> >
> > So, in G29, the formula would be =SUM(FirstHoursBooked:F29), in G30
> > =SUM(FirstHoursBooked:F30) and so on. This is nested in a loop which
> performs
> > the command a certain number of times, moving down a cell each time,
> > depending on whether the value in column F (Actual Hours) is greater than
> > zero, so, "No value in F, no Cumulative formula in G"
> >
> > I'm having trouble coding this - I can do it in R1C1 notation thus:
> >
> > For CumFormLoop = 1 To CumFormCount
> > ActiveCell.FormulaR1C1 = "=SUM(R29C6:RC[-1])"
> > ActiveCell.Offset(1, 0).Select
> > Next
> >
> > But I want to use a range name instead of "R29", as if anyone alters the
> > structure of the worksheet, row 29 may no longer be the first cell in the
> > range to be summed.
> >
> > I need some hybrid of R1C1 nottation that would look like this (although
> > this obviously doesn't work)
> >
> > ActiveCell.FormulaR1C1 = "=SUM(FirstHoursBooked:RC[-1])"
> >
> > Can anyone help - I demo this to the boss tomorrow!
> >
> > Thanks in advance
> >
> > Pete
> >
> >
> >
> >
> >
> >
> >
>
>
>

Pete,

It is simply running the formula that I originally gave you within VBA.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thanks for this, although I must confess I don't quite understand it yet.
> Only just replying to you as I was on half a day's leave yesterday.
>
> I'll give it a go, anyway.
>
> Regards
>
> Pete
>
>
>
> "Bob Phillips" wrote:
>
> > iRow = Evaluate("MATCH(MIN(IF(B10:B25<>0,B10:B25)),B10:B25,0)")
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Peter Rooney" <[email protected]> wrote in message
> > news:[email protected]...
> > > Good afternoon, all
> > >
> > > I need to produce a VBA statement that will input a formula into a
range
> > of
> > > cells in a Cumulative Hours column which is essentially "Sum
everything
> > from
> > > the cell on the left up to the cell named "FirstHoursBooked" "
> > >
> > > So, in G29, the formula would be =SUM(FirstHoursBooked:F29), in G30
> > > =SUM(FirstHoursBooked:F30) and so on. This is nested in a loop which
> > performs
> > > the command a certain number of times, moving down a cell each time,
> > > depending on whether the value in column F (Actual Hours) is greater
than
> > > zero, so, "No value in F, no Cumulative formula in G"
> > >
> > > I'm having trouble coding this - I can do it in R1C1 notation thus:
> > >
> > > For CumFormLoop = 1 To CumFormCount
> > > ActiveCell.FormulaR1C1 = "=SUM(R29C6:RC[-1])"
> > > ActiveCell.Offset(1, 0).Select
> > > Next
> > >
> > > But I want to use a range name instead of "R29", as if anyone alters
the
> > > structure of the worksheet, row 29 may no longer be the first cell in
the
> > > range to be summed.
> > >
> > > I need some hybrid of R1C1 nottation that would look like this
(although
> > > this obviously doesn't work)
> > >
> > > ActiveCell.FormulaR1C1 = "=SUM(FirstHoursBooked:RC[-1])"
> > >
> > > Can anyone help - I demo this to the boss tomorrow!
> > >
> > > Thanks in advance
> > >
> > > Pete
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >