Increase all cells by a % and round up?

S

sacredarms

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
 
T

tjtjjtjt

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
 
Top