Macro for minus

Microsoft Book: office excel 2003 inside...
March 21, 2010 at 01:19:11
Specs: Windows XP
Can someone provide a macro to do the following?

If col.L contains M then cells value in:
col.H just(one row) below row containing M in col.L minus
Col.E row containing M in col.L.
Then the result at
Col.E one row above row containing M in col.L.

All the formula used should be retained.

See More: Macro for minus

Report •

March 21, 2010 at 05:12:25

I am not sure why you are asking for a macro to create a formula. Perhaps I am missing something.

In cell E2 enter this:


Now, if L3 contains "M", cell E2 will return the value H4 minus E3,
i.e. the value in column H one row below M in column L,
minus the value in column E on the same row as M in column L.

As you have the result in column E and one of the values used in the formula is also in column E, you can't drag the formula down to extend it, unless the result of the formula is also used as the value in column E on the same row as M, for the next formula.

Anyway, the above formula does what you asked.

You didn't say what to do if the cell in column L does not contain "M", so the formula returns and empty string "".


Report •

March 21, 2010 at 23:37:57
Using formula will give circular reference error. I thought macro
would overcome this. The calculation is done this way:

If L5 contain M then,

 1800 (E4)                                          2000*2 (H4) - 2000 is from cell E6
 +200 (Col.E row containing M in col.L)		   +500 (H5)
_____                                             _______
 2000 (E6)                                          4500 (H6)

I want 4500(H6)-200(E5) = 4300 to place at cell E4. My data spreads to many rows in similar way.

Report •

March 22, 2010 at 05:49:49

First a question - in your post you have E4 =1800 and E4=4300

1800 (E4)
4500(H6)-200(E5) = 4300 to place at cell E4

was there a reason for this.

I think that I have managed to construct the set of formulas that you refer to in your response (Response #2)

The formulas do indeed contain a circular reference.
Creating the formulas using a macro (you said All the formula used should be retained.), will not stop them containing a circular reference.

Circular references are not the result of how you create formulas - they are the result of what the formulas refer to.

Are you sure that you want a circular reference.
With a circular reference, when a value that the formula refers to changes, the result of the formula changes and this in turn causes another cell that the formula refers to, to change. This causes the result of the formula to change and again this causes that cell that the formula refers to, to change.

As a result the change in the formula is endless, although the changes may get increasingly small.

I believe that it is possible to limit the number of repeated re-calculations that Excel performs, so that the loop is not endless.

Limiting recalculation will affect all calculations in your workbook and may result in different results for some formulas, elsewhere in your Workbook. The results for the cells that are in the loop of the circular formula will have results that are affected by the limitation in the number of times Excel goes round the loop, recalculating the formulas.

a. Is this what you really want.
b. Do you understand the consequences of limiting the recalculation of formulas.

I suggest you review what you are trying to achieve and decide if you can get the required result without a circular reference.


Report •

Related Solutions

March 22, 2010 at 20:59:59
I want macro to calculate H6 - E5 then put its result(4300) at E4 by replacing 1800.
After running the macro the calculation at my response no.2 shall be like this:

If L5 contain M then,

 4300 (E4)                                          4500*2 (H4) - 4500 is from cell E6
 +200 (Col.E row containing M in col.L)		   +500 (H5)
_____                                             _______
 4500 (E6)                                          9500 (H6)

I feel that E4 can't contain a formula that linked to H6 as it will cause circular reference error or goes on endless calculations. Thats why i need macro. Can this be done without macro?

Report •

March 23, 2010 at 07:10:35

You can use a macro to do the calculation, when "M" is entered in a cell in column L

Right-click on the Worksheet name tab and select 'View code'
Enter this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'stop events, so that changes made by this macro do not trigger the code again
Application.EnableEvents = False

'test if changed cell is in column L and contains "M"
If Target.Column = 12 And Target.Text = "M" Then
    '(col H row +1 - col E same row)  and place in col E row -1
    ActiveSheet.Range("E" & Target.Row - 1) = _
            ActiveSheet.Range("H" & Target.Row + 1) - _
            ActiveSheet.Range("E" & Target.Row)
End If

Application.EnableEvents = True
Exit Sub

'error handler
Application.EnableEvents = True
End Sub

Now when "M" is entered in a cell in column L the result is placed in column E, one row above the row containing the "M".

Here is an example of a before and after:

	E	F	G	H
4	3300			7000
5	200			500
6	3500			7500
	E	F	G	H
4	7300			15000
5	200			500
6	7500			15500

Because of your formulas, the result, in this example 7300 in cell E4, which came from 7500 (H6) - 200 (E5)
is no longer 'correct' because H6 is now 15500 and E5 is still 200, so the result (H6-E5) is now 15,300, not the 7300 shown.

If you use this macro to enter a formula such as =H6-E5 in cell E4, it will not calculate. It shows 0 due to the circular reference.


Report •

March 24, 2010 at 05:25:58
Thanks Humar for your reply. Can i run it without worksheet_change macro?

Report •

March 25, 2010 at 06:14:20

You can run the code from a worksheet button, but as you are no longer using a cell in column L as the reference point, the code is quite different. The code I posted uses the Cell in column L, containing "M" as the starting point for identifying which cells are used in the calculation and which cell receives the result.

Assuming that the cell to receive the result (E4 in your example) is the selected cell, the following code will perform the calculation H6-E5 and place the result in cell E4.

If the selected cell was E5, the calculation would be H7-E6 and the result would be placed in cell E5.

Here is the code:

Private Sub CommandButton1_Click()
'based on the active cell, subtract the value
'in the cell in the same column one row down
'from the value in the cell three columns right and two rows down
'and place the result into the active cell

ActiveCell.Value = ActiveSheet.Cells(ActiveCell.Row + 2, ActiveCell.Column + 3) - _
                ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column)

End Sub

Note however that changes to the cells such as H6 and E5 will not result in the value in E4 from changing.
There is no link between the cells and Excel will not be able to update the result and, as I showed before, the value in E4 will not be H6-E5, as the value in H6 changes as soon as the macro places a new value in E4.


Report •

March 25, 2010 at 21:29:35
Thanks Humar, Both code works perfect.

Report •

Ask Question