I am currently starting learning how to program with VBA. I am able t
see how powerful a tool it can be. This is my problem. I currentl
create spreadsheets where I have to repeatedly enter a value in on
cell and subtract the three cells to the left of it. To state m
problem clearly, I am given the annual operating information for
company and need to "back out" each quarter's results. Therefore, whe
looking at an annual report, I only have the annual results for the 4t
quarter, not the observed amount. Therefore, to back out this value,
have to subtract the previous three quarters from it (three cells t
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 th
values in the three cells to the left of it. However, to my limite
programming knowledge, I have been trying to use VBA to recognize th
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 b
willing ot have a input box prompt me for the annual total to be use
in the formula.

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

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

Any help will certainly be appreciated.

Thanks again and Best Wishes,

Broc

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

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/

Thank you very much for your prompt response. It is greatly
appreciated.

Regards,

Brock


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