I need to subtract .50 from every number in column c every 2 weeks

I need to subtract .50 from every number in column c every 2 weeks. Column c is different every 2 weeks in how many numbers are in that column. I also need to keep these as whole numbers as I need to another program to pull these numbers from this sheet. I am uploading an example of my file.
jodyreidIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Omar SoudaniSenior System EngineerCommented:
There is no attachment.
KoenChange and Transition ManagerCommented:
you will need to decide how you want that data...
subtracting the .5 is easy, but here are some question:
do you just want to change column c ? or do you want a history built so that you see what result was obtained at what date?
how do you want the rounding to be? if I subtract .5 from 9.2, the result is 8.7 - does that turn into 8 (which would result in a formula =INT(C2) or do you want to apply normal rounding, result 9 , formula=round(c2,0) ?
how do you want to trigger the subtraction? automatically? manually? macro driven?
jodyreidIT ManagerAuthor Commented:
I would like to just change column C, don't need a history as I will have it already. I need the numbers to be 8.7. I would like to have it triggered manually with a button if possible. Thank you.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jodyreidIT ManagerAuthor Commented:
sorry I did not upload file. It is there now.
Thank you
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As per your sample file, column C is empty.
Do you mean subtract 0.50 from column B?
Rob HensonFinance AnalystCommented:
Maybe I am missing something but simplest way I can see is:

1) Enter 0.5 into a spare cell
2) Copy that cell to the clipboard, Ctrl + C
3) Select the cells in column B
4) Select Edit > Paste Special
5) In Paste Special window, in middle section select Subtract, click OK

All values in column B will be reduced by 0.5

Job Done, matter of seconds and maintains Undo history if required which using VBA would not.
jodyreidIT ManagerAuthor Commented:
yes column B. Thank you.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached and see if something like this would work for you.
Click the button to subtract 0.50 from column B values. Each time you click the button, 0.50 would be subtracted from column B values and the caption of the button will be updated to let you know when did you update the column B.
jodyreidIT ManagerAuthor Commented:
Yes that is great. How do i get that to work with my excel file.
Thank you.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To do that follow these steps...

1) Insert an ActiveX Command Button on the sheet.
2) Select the command button, open properties and renamed the button to "Update Values".
3) While still in design mode, double click the command button and paste the code given below in between the two lines of code inserted by default.
4) Turn off the design mode and you are ready to use the command button.

If you have difficulty in inserting an ActiveX Command Button on the sheet, just Google it and you will find various tutorials to do that.

Please remember that the following code will modify the column B values. If the column is different, tweak the code accordingly.

Dim lr As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
Range("B2:B" & lr).Value = Evaluate("if(B2:B" & lr & ">0,If(isnumber(B2:B" & lr & "),B2:B" & lr & "-0.5,B2:B" & lr & "),"""")")
Me.CommandButton1.Caption = "Last Updated On: " & Now

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jodyreidIT ManagerAuthor Commented:
Thank you.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
Rob HensonFinance AnalystCommented:
Just saying....

A bit like a sledgehammer to crack a nut when a simple copy and paste will do the trick. But heh, each to their own.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.