Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply All values by a cell
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!" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply All values by a cell
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) "Ben Allen" wrote in message ... 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!" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply All values by a cell
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Ben Allen" wrote in message ... 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!" --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.656 / Virus Database: 421 - Release Date: 09/04/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply All values by a cell
Ken Wright wrote:
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!" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply All values by a cell
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/ _______ Ben Allen wrote: Ken Wright wrote: 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!" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply All values by a cell
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 :- ) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Ben Allen" wrote in message ... Ken Wright wrote: 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!" --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.656 / Virus Database: 421 - Release Date: 09/04/2004 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply All values by a cell
Ken Wright wrote:
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!" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiply All values by a cell
Jon Peltier wrote:
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!" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to multiply two values and round the result | Excel Discussion (Misc queries) | |||
Multiply Cell Values which include text units | Excel Worksheet Functions | |||
Multiply values from VLookup | Excel Worksheet Functions | |||
multiply all values in all cells by a factor | Excel Discussion (Misc queries) | |||
Multiply all values by 10 | Excel Discussion (Misc queries) |