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.
Test1.xlsx
jodyreidIT ManagerAsked:
Who is Participating?
 
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

0
 
Omar SoudaniSystem Support EngineerCommented:
There is no attachment.
0
 
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.
0
 
jodyreidIT ManagerAuthor Commented:
sorry I did not upload file. It is there now.
Thank you
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As per your sample file, column C is empty.
Do you mean subtract 0.50 from column B?
0
 
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.
0
 
jodyreidIT ManagerAuthor Commented:
yes column B. Thank you.
0
 
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.
SubtractFromEachCell.xlsm
0
 
jodyreidIT ManagerAuthor Commented:
Yes that is great. How do i get that to work with my excel file.
Thank you.
1
 
jodyreidIT ManagerAuthor Commented:
Thank you.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.