Sub Subtract3()
ActiveCell.Formula = "=" & ActiveCell.Value & " - Sum(" & _
ActiveCell.Offset(0, -3).Resize(1, 3).Address(0, 0) & ")"
End Sub


Assign a shortcut to this using the Options button under Tools=
Macro=Macros.

--
Regards,
Tom Ogilvy




"Btibert " wrote in message
...
I am currently starting learning how to program with VBA. I am able to
see how powerful a tool it can be. This is my problem. I currently
create spreadsheets where I have to repeatedly enter a value in one
cell and subtract the three cells to the left of it. To state my
problem clearly, I am given the annual operating information for a
company and need to "back out" each quarter's results. Therefore, when
looking at an annual report, I only have the annual results for the 4th
quarter, not the observed amount. Therefore, to back out this value, I
have to subtract the previous three quarters from it (three cells to
the left of it).

Ultimately, what I would like to do is type the "running annual total"
in the appropriate Q4 cell, type a shortcut, and have it subtract the
values in the three cells to the left of it. However, to my limited
programming knowledge, I have been trying to use VBA to recognize the
value in the cell and then subtract it from the cells to the left,
however, it is not functioning properly. If need be, I would even be
willing ot have a input box prompt me for the annual total to be used
in the formula.

Since I would be performing this action often, I believe this is a
relative reference issue, and not an absolute cell reference.
Additionally, assuming that the code is relatively simple, I think I
have enough knowledge (or at least be able to figure out) how to create
another macro that lets me subtract only two columns to the left, then
only one column to the left.

I apologize for the long explanation. As you can see, I perform the
task, for example "9.5 - A1 -A2 - A3" repeadtely in my workbooks and
want to simply the process to increase the efficiency of my
keystrokes.

Any help will certainly be appreciated.

Thanks again and Best Wishes,

Brock


---
Message posted from http://www.ExcelForum.com/