How to do simple math in Excel VBA

Question

I have a value in any cell which is greater than 60. For my purpose, I need to subtract 60 from such values until the value is less than 60. So, for example, say I have a cell containing a value of 1000. The value I want to use would be 40 because 40 is the first value that is less than 60 after repeatedly subtracting 60.

I envision a VBA solution that would subtract 60 repeatedly and then stop and display the final value, 40, as this satisfies the condition.

The data range is large and values above 60 can occur anywhere in the range. I want this to be done automatically when I open the spreadsheet so that all formulas that might refer to values over 60 use the remainders.

Answer 1

You don't need VBA to do what you want. You want to subtract 60 repeatedly until the remainder is less than 60. You could write VBA code to do that, but you can do that in one step with a formula:

    =MOD(ED1,60)

If you divide 1000 by 60, you get 16.666... That means that you would need to subtract 60 sixteen times to get something less than 60. The MOD function produces the remainder after the division (in this case, 40), which is equivalent to those subtractions.

You can use this in two ways. One is to incorporate this MOD expression (without the equal sign) in your formulas in place of the simple cell reference to data cells that can exceed 60. This will use the translated value.

If you have numerous formulas that reference each cell and don't want to have the overhead of all of that repeated recalculation of the same values, create a set of translated values--a duplicate data range that contains the above formula. That will translate the data once and then all of your formulas can reference these numbers instead of the raw data. If you don't want the overhead of those calculations being refreshed, do a copy | paste special | values. You can paste the results over the raw data and then delete the intermediate calculations.

Answer 2

I have great news! Because the value in ED1 is a constant, the formula will always result in the value:

960

Let's assume the long range is A1:Z100

Here is the VBA based solution:

Private Sub Workbook_Open()
    Sheets("Sheet1").Range("A1:Z100").Value = 960
End Sub

This is a workbook event macro.

Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (workbook code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!