Multiply All values by a cell

[Ben Allen]

I have a table filled with values that are formated by currency. I need to
multiply each of these values (all in columns G to K) by a value on another
sheet which contains the latest exchange rate.I want this to happen in a
macro when a button is clicked.
Thanks for any help.
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Bob Phillips]

Hi Ben,.

Something like

For Each cell In Worksheets("Sheet1").Range("C1:H10")
cell.Value = cell.Value * Worksheets("Sheet2").Range("F2").Value
Next cell

adjust to suit

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

And the reason you want to do this via VBA rather than a simple formula which
will update automatically is.......

Assuming you are on sheet1 with your table of data, and your exchange-rate is in
some cell on another sheet. Go select the cell with the exchange rate in and
name it xrate. Now go back to sheet 1, and in any cell type =xrate. Now copy
that cell (Not the contents, but the cell), select all your data in Cols G to K
and do Edit / Paste Special / Multiply. Each entry in Cols G to K will now be
preceded with an = and end with *(xrate). You change a value in the xrate cell
and it is all automatically updated.

 

[Ben Allen]

And the reason you want to do this via VBA rather than a simple
formula which will update automatically is.......

Assuming you are on sheet1 with your table of data, and your
exchange-rate is in some cell on another sheet. Go select the cell
with the exchange rate in and name it xrate. Now go back to sheet 1,
and in any cell type =xrate. Now copy that cell (Not the contents,
but the cell), select all your data in Cols G to K and do Edit /
Paste Special / Multiply. Each entry in Cols G to K will now be
preceded with an = and end with *(xrate). You change a value in the
xrate cell and it is all automatically updated.

Because i want a button to change the currency into Euros by multiplying by
the value and a button to convert back into dollars my dividing by the
value.

Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Jon Peltier]

Ben -

You still don't need VBA. Make the Forms toolbar visible (View menu >
Toolbars). Pick your favorite control(s) to enable selection of
currency. I used two option buttons: one for Euro, the other for
Dollars. Right click one of the option buttons, select Format Control
from the pop up menu. On the Control tab, click in the Cell Link box and
select a cell. I used $K$1. OK.

Then I put the Euro rate in $L$1 and the Dollar rate in $L$2 (actually I
used 1 for Dollar). I named cell $M$1 xrate, because that's the name Ken
used, and I entered a formula in the cell:

=INDEX($L$1:$L$2,$M$1)

This cell changes from 1 when the Dollar option button is selected
(button 2, because I drew it second), to the ratio when the Euro button
is selected (button 1). Any formulas that include xrate as a factor
change when the selected option button is switched.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/Excel/Charts/
_______

 

[Ken Wright]

LOL - Ben, sorry if that appeared abrupt, as it really wasn't meant to be. Its
just that I hate using VBA when the same result can be achieved without it, and
as Jon has described, this can be done quite easily without, and means you don't
permanently change any of your source data ( Something I detest doing at any
time :-> )

 

[Ben Allen]

LOL - Ben, sorry if that appeared abrupt, as it really wasn't meant
to be. Its just that I hate using VBA when the same result can be
achieved without it, and as Jon has described, this can be done quite
easily without, and means you don't permanently change any of your
source data ( Something I detest doing at any time :-> )

No Worries, thanks for your help. I eventualy did it by using an IF
statement in the cell, when the user clicks a vba button a cell on another
sheet (which i was using to store loggin infomation etc) is filled in with
the value "Euro". the If statement in the cells then says if this cell=Euro,
do lookup * exchange rate, otherwise do lookup. Thats the basic version
anyway.
Thanks Again.
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Ben Allen]

Ben -

You still don't need VBA. Make the Forms toolbar visible (View menu >
Toolbars). Pick your favorite control(s) to enable selection of
currency. I used two option buttons: one for Euro, the other for
Dollars. Right click one of the option buttons, select Format Control
from the pop up menu. On the Control tab, click in the Cell Link box
and select a cell. I used $K$1. OK.

Then I put the Euro rate in $L$1 and the Dollar rate in $L$2
(actually I used 1 for Dollar). I named cell $M$1 xrate, because
that's the name Ken used, and I entered a formula in the cell:

=INDEX($L$1:$L$2,$M$1)

This cell changes from 1 when the Dollar option button is selected
(button 2, because I drew it second), to the ratio when the Euro
button is selected (button 1). Any formulas that include xrate as a
factor change when the selected option button is switched.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/Excel/Charts/

Thanks Jon, i got it sorted (see other post)
Thanks Again
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"