cell formulas

[Lynn]

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

 

[Gord Dibben]

Try entering 1.03 in an empty cell.

Copy that.

Select the columns to change and edit>paste special>multiply>ok>esc.

Delete the 1.03 after.


Gord Dibben MS Excel MVP

 

[Shane Devenshire]

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

 

[Gord Dibben]

Thanks Shane.

Missed the "merged" part.


Gord