If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
cell formulas
I have an Excel spreadsheet of a price list with various columns of currency
that I need to increase +3% and a separate sheet increased +6%. It's originally set up in various sections that have multiple currency columns. The normal formulation doesn't seem to work because some cells are merged. This is a nightmare spreadsheet from a company that we sell for. Anyone that can help me will be a SAINT. With Appreciation, LR |
#2
|
|||
|
|||
cell formulas
Try entering 1.03 in an empty cell.
Copy that. Select the columns to change and editpaste specialmultiplyokesc. Delete the 1.03 after. Gord Dibben MS Excel MVP On Fri, 12 Jun 2009 12:37:01 -0700, Lynn wrote: I have an Excel spreadsheet of a price list with various columns of currency that I need to increase +3% and a separate sheet increased +6%. It's originally set up in various sections that have multiple currency columns. The normal formulation doesn't seem to work because some cells are merged. This is a nightmare spreadsheet from a company that we sell for. Anyone that can help me will be a SAINT. With Appreciation, LR |
#3
|
|||
|
|||
cell formulas
Hi,
There is a problem with Gord's suggestion because you said there are merged cells. If you enter 1.03 in a cell and highlight a range that contains merged cell and try the Edit, Paste Special, Multiply trick, all the merged cells will become unmerged and 0 will appear in the all "extra" cells of the merged cell. Depending on how many cells are merged and how this can become rather ugly. However, here is a VBA approach which should do what you need. Just select the cells, merged, text or numbers and run it. Sub IncreasePercent() Dim cell as Range Dim myPercent as Double myPercent = InputBox("Enter the percent to increase: for 1% enter 1") For Each cell In Selection If IsNumeric(cell) And cell "" Then cell.Value = cell * (1 + myPercent / 100) End If Next cell End Sub -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lynn" wrote: I have an Excel spreadsheet of a price list with various columns of currency that I need to increase +3% and a separate sheet increased +6%. It's originally set up in various sections that have multiple currency columns. The normal formulation doesn't seem to work because some cells are merged. This is a nightmare spreadsheet from a company that we sell for. Anyone that can help me will be a SAINT. With Appreciation, LR |
#4
|
|||
|
|||
cell formulas
Thanks Shane.
Missed the "merged" part. Gord On Fri, 12 Jun 2009 22:58:01 -0700, Shane Devenshire wrote: Hi, There is a problem with Gord's suggestion because you said there are merged cells. If you enter 1.03 in a cell and highlight a range that contains merged cell and try the Edit, Paste Special, Multiply trick, all the merged cells will become unmerged and 0 will appear in the all "extra" cells of the merged cell. Depending on how many cells are merged and how this can become rather ugly. However, here is a VBA approach which should do what you need. Just select the cells, merged, text or numbers and run it. Sub IncreasePercent() Dim cell as Range Dim myPercent as Double myPercent = InputBox("Enter the percent to increase: for 1% enter 1") For Each cell In Selection If IsNumeric(cell) And cell "" Then cell.Value = cell * (1 + myPercent / 100) End If Next cell End Sub |
Thread Tools | |
Display Modes | |
|
|