Define Name to current selection

Hi All,

Can anyone advise on the proper code to define a name to current selection? I've tried Add Name code but it's not working the way I want. It always refers to the same range like for example
RefersTo:="=sheet1!$a$1:$c$20"

Thanks.

Hi Eduard,

try something like this:

Please Login or Register  to view this content.

this will refer to the seleccted cells in the worksheet

No problem, glad to help

Sorry, Stuie.

Would you maybe know how do I use the defined names in a formula? Normaly if I define an area as Price and another one as Quantity than I just type in the formul =Price*Quantity. But when I try to do it with a macro it doesn't work.

This is the code I'm using:

FormulaR1C1 = "=Price*Quantity"

Thanks,
Eduard

i usally dont bother with the FormulaR1C1, i have found if you put the forumla un as text that tends to work like:

Please Login or Register  to view this content.

and just change the "A1" to the cell you are using

This doesn't work either. I figured out that the problem is that the names are defined by absolute addresses, i.e. $A$1:$A$20 and $B$1:$B$20. While in order for the formula to work it should be relative address.

Any idea how to do that?

Thanks,
Eduard

so are you defining Price & Quantity as more than one cell?

Correct. These are 2 different ranges of cells.

okie doke, when you write price*quantity have you got a quick example of how this looks on the spreadsheet as i think it just takes the first cell of each range argument and uses those.

Stuie,

See attached a sample.

I have a macro code that defines A2:A5 as Price and B2:B5 as Quanity based on the header row. I know it would be easier to enter a formula in C2=A2*B2. But it will not work in case when there are more columns between Price and Quantity.

So I thought using defined names will be better.

Thanks,
Eduard

if there were for example 3 columns with a price in and 1 column with a qty would you not do =SUM(Price) * Quantity

or am i looking at this wrong?

No. The heading of the 3rd column should actulay read Sum=Price*Column