Increase all cells by a % and round up?
This macro works for me. I have it set to round to the nearest integer.
Change the 1 in the line "Then x.Value =
Application.WorksheetFunction.Ceiling (x.Value + x.Value * s, 1)" to whatever
number you want to round up to. Check out Excel's help on CEILING and/or
ROUNDUP for more info.
Hope this helps.
Post back if you need more information about using macros.
Sub IncreaseAndRoundUp()
Dim x As Range
Dim s As Variant
s = InputBox("Please type a percentage value as" & _
"a decimal." & vbCr & "e.g.: 0.10")
If Left(s, 1) = "." Or Mid(s, 2, 1) = "." Then
For Each x In ActiveSheet.UsedRange.Cells
If Application.WorksheetFunction.IsNumber(x.Value) _
Then x.Value = Application.WorksheetFunction.Ceiling _
(x.Value + x.Value * s, 1)
Next x
Else
MsgBox ("Please type a percentage value as" & _
"a decimal." & vbCr & "e.g.: 0.10")
End If
End Sub
--
tj
"sacredarms" wrote:
I have a spreadsheet that I need to increase all cells by a fixed percentage
and round up. I know I can use the paste special for the increase but is
there a way I can do both at the same time? Any help appreciated.
Thanks
Joe
|