Grouping

[Tim Stoner]

Good Afternoon All,
I have a pricelist of products that I update frequently with price
adjustments,
my question is can I group all the costs into a named group and then use
a formula to say add five percent ...I can do this by manually
highlighting cells and use the paste multiply but want to find a quicker
way to do this

example ..

group named "pine" *1.05 to add 5% to each cell in that group
is this possible ???
can you please share your knowledge if it can be done it would be
appreciated

many thanks
TIM

 

[isabelle]

hi Tim,

do you want do this with formulas or with a macro(vba) ?

 

[GS]

Tim Stoner wrote :

Good Afternoon All,
I have a pricelist of products that I update frequently with price
adjustments,
my question is can I group all the costs into a named group and then use a
formula to say add five percent ...I can do this by manually highlighting
cells and use the paste multiply but want to find a quicker way to do this

example ..

group named "pine" *1.05 to add 5% to each cell in that group
is this possible ???
can you please share your knowledge if it can be done it would be appreciated

many thanks
TIM

Hi Tim,
One technique I use is to store the price change % in a named cell,
then click a button to update the new prices. so...

Define a named range with sheet-level scope, say...
name: Sheet1!PriceAdjust
RefersTo: =$A$1 'change to suit

In a standard module:

Assign the following macro to a button

Sub UpdatePrices()
With ActiveSheet
.Range("pine").Value = _
.Range("pine").Value * (1 + .Range("PriceAdjust"))
End With
End Sub

...where Range("PriceAdjust") = 5%

 

[Cimjet]

Hi Garry
I like your macro for its simplicity but I get an error 13 "Type mismatch".
Everything works ok up to this :
Sub UpdatePrices()
With ActiveSheet
.Range("pine").Value = _
.Range("pine").Value
---------------------------
anything I put after Value, will give me an error "* (1 +
..Range("PriceAdjust"))"
I tried something simple like "+ 15", it wont accept anything.
Regards
Cimjet

 

[Gord Dibben]

"pine" is a range of cells and you have to iterate through them.

Sub UpdatePrices()
Dim ocell as Range
With ActiveSheet
For Each ocell In .Range("pine")
ocell.Value =ocell.Value * (1 + .Range("PriceAdjust"))
Next
End With
End Sub


Gord Dibben MS Excel MVP

 

[Cimjet]

Hi Gord
It was too simple to be true, the "For, Next" works great.
Thanks Gord

 

[GS]

Gord Dibben formulated the question :

"pine" is a range of cells and you have to iterate through them.

Sub UpdatePrices()
Dim ocell as Range
With ActiveSheet
For Each ocell In .Range("pine")
ocell.Value =ocell.Value * (1 + .Range("PriceAdjust"))
Next
End With
End Sub


Gord Dibben MS Excel MVP

Thanks for the catch, Gord!
Tim, I apologize for omitting the loop. I should've seen the need for
it right away but for some reason it escaped me.

 

[Gord Dibben]

Glad to assist.

Gord

Hi Gord
It was too simple to be true, the "For, Next" works great.
Thanks Gord

 

[Tim Stoner]

hi Tim,

do you want do this with formulas or with a macro(vba) ?

Isabelle can this be done with a formula ????
Any help appreciated
As I am reasonably proficient I am by no means an excel expert so any
help welcome
Thank you Tim

 

[isabelle]

hi Tim,

you will have to define a named range like "rate" with value = 1
modify the price list has formulas such as "= 10.50 * rate"
and now just change the rate value for to update list
but if the list is very long, it would be better to use a macro as suggested Gord and Garry

 

[Tim Stoner]

Good evening all,
many thanks for your help with this problem ,with this remedy i can
update the prices as requested only when I have done that it will not
allow me to do this again as it removes the named cells group from the
cells ie ..before running macro pine group is named after running macro
the cells are not named pine anymore

thanks for taking the time to help ..I am nearly there ;-)

 

[Gord Dibben]

Running the macro will do nothing to remove the name "pine" from the group of
cells.

Assuming A1:A10 is named "pine" you can run the macro multiple times and it
will just keep adding whatever percentage you have stored in "PriceAdjust" cell.

Send me your workbook by email if you want me to have a look at it.

change phnorton to gorddibb for my email address.


Gord

 

[Tim Stoner]

Gord,
thanks for your offer , blushing when I shut down Excel and rebooted
this morning all was good .I don,t know what was wrong but it is all
good now ...maybe the old windows thing if it aint working reboot
is the go
I would like to thank you guys for taking the time to help people like
me forward my knowledge on an under used program

Tim

 

[Gord Dibben]

Good to hear you're sorted out.

Happy to assist.


Gord