VBA to input a Cumulative formula

[Guest]

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 Phillips]

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

--

HTH

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

 

[Guest]

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

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

--

HTH

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

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 Phillips]

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)

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

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

--

HTH

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

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