Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Carrying formulas over to new cell when old cell is deleted: possi | Excel Worksheet Functions | |||
CLICK CELL, GO TO ANOTHER CELL. NO FORMULAS. | Excel Discussion (Misc queries) | |||
How to trace a cell? Which cells use a special cell in formulas? | Excel Discussion (Misc queries) | |||
Copying formulas from cell to cell to cell to....... | Excel Discussion (Misc queries) | |||
change cell value greater than another cell value using formulas | Excel Worksheet Functions |