Increase all cells by a % and round up?
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 |
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 |
All times are GMT +1. The time now is 11:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com