change parameters of a range to be summed

[Guest]

hi, I have a macro in a worksheet that selects the last populated cell in a
range (D18:V18), copies the formula from this cell and pastes it into the
next empty cell to the right. I wish to then create a formula in cell Y18
that will sum only the range from the newly populated cell to the end of the
range (V18). this formula needs to update and use the newly populated cell as
it's starting point whenever the macro is run. am I asking too much from a
formula and need to use vba instead or is this possible?
thanks in advance

sevi

 

[Guest]

Hi Sevi,

Since you are running a macro anyway it is quite easy to do what you want by
defining a name for the last cell in the range. Example suppose you are
summing the values in the range D18:V18 then formula is:-
=SUM(D18:V18).

If you define a name for D18 say First_Cell then the formula can be written:-
=SUM(First_Cell:V18).

In the macro having pasted the value into another cell, then select the
required cell and reassign the name First_Cell to the new cell address.

The easy way to get the code to define/reassign the name is record it. You
will get something like this:-

ActiveWorkbook.Names.Add Name:="First_Cell", _
RefersToR1C1:="=Sheet1!R18C11"

However you need to edit the range so that it becomes dynamic to the
required cell. By selecting the cell with your macro before reassigning it
then you can call the cell address ActiveCell like this:-

ActiveWorkbook.Names.Add Name:="First_Cell", _
RefersToR1C1:=ActiveCell

Just in case you have not used Named Ranges:-

In xl 2007 you will find it in the Formulas ribbon.

Earlier versions of xl under the menu item Insert->Name->Define.

Regards,

OssieMac

 

[Guest]

Thanks OssieMac,

your bloods' worth bottling mate!

regards,

sevi