You could do it with a macro. I'm not sure if you need to work with more
than one of the columns, but this code will allow you to pick one at a time
to work with.
May want to try it out on a copy of the real file, just in case. To get the
code into the workbook, press [Alt]+[F11] to open the Visual Basic Editor.
From its menu, choose Insert | Module. The copy the code below and paste it
into the module, make any changes needed, such as what row your first price
is on or what the default column should be, then close the VB Editor. To do
it, go to the sheet with your pricing, choose Tools | Macro | Macros and
click the macro named MakeNewPrices and then click the [Run] button.
Sub MakeNewPrices()
Const firstPriceRow = 2 ' change as needed
Dim anyEntry As Variant
Dim whatColumn As String
Dim percentIncrease As Single
Dim lastRow As Long
Dim LC As Long ' loop counter
'prices in column chosen will be altered
'default is column "A", you can change that if desired:
whatColumn = InputBox("Which column has prices to be changed?", _
"Price Column", "A")
If whatColumn = "" Then
Exit Sub ' [Cancel]d
End If
'if no value entered, no increase applied
anyEntry = InputBox("What percent increase to apply (" _
& "Enter 5 for 5%)?", _
"Increase", 0)
If anyEntry = "" Then
Exit Sub ' [Cancel]d
End If
percentIncrease = anyEntry
If percentIncrease = 0 Then
Exit Sub ' zero, negative is allowed for cost reduction
End If
lastRow = Range(whatColumn & Rows.Count).End(xlUp).Row
For LC = firstPriceRow To lastRow
Range(whatColumn & LC) = _
Int(Range(whatColumn & LC) * _
(1 + percentIncrease / 100)) + 1
Next
End Sub
