Microsoft Book: office excel 2003 inside...

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.

Hi, 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:

=IF(L3="M",H4-E3,"")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,

unlessthe 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 "".

Regards

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.

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

1800 (E4)and4500(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 saidAll 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

allcalculations 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.

Regards

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?

Hi, 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 ErrHnd: Err.Clear Application.EnableEvents = True End SubNow 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:

Before E F G H 4 3300 7000 5 200 500 6 3500 7500 After E F G H 4 7300 15000 5 200 500 6 7500 15500Because 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.

Ifyou 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.Regards

Thanks Humar for your reply. Can i run it without worksheet_change macro?

Hi, 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 willnotresult 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 willnotbe H6-E5, as the value in H6 changes as soon as the macro places a new value in E4.Regards

Thanks Humar, Both code works perfect.

Ask Your Question

Weekly Poll

Do you agree with Google's decision to shut down Google+?

Discuss in The Lounge

Poll History