|
Dependant or Refenced Cells Calculations
Hi,
I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" .. I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi
I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi Roger,
I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi Reha
There is no need to use absolute addressing. Try inserting the following code into your worksheet, and it will work for all rows in the sheet Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long Application.EnableEvents = False mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 3) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 3) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub I have put a rem statements after each Case, so you can see which column triggers the event and which column gets the result and what makes up the calculation. If I have made a mistake in any of the formulae, or their target address, you should be able to work out how much to alter the + or - column offset to give the result you want. We could limit the event being triggered to a range of rows 2 to 550 if that is required by entering the following line of code before the Application.EnableEvents = False line If Target.Row < 2 Or Target.Row 550 Then Exit Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi Reha
Many apologies. I added the line to test for columns outside the range 9 to 16 as an afterthought, and this is clearly in the wrong place. It needs to come before Application.EnableEvents = False, otherwise you could exit the sub without switching this back on again. The correct order should be mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Application.EnableEvents = False Select Case mcol -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Reha There is no need to use absolute addressing. Try inserting the following code into your worksheet, and it will work for all rows in the sheet Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long Application.EnableEvents = False mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 3) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 3) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub I have put a rem statements after each Case, so you can see which column triggers the event and which column gets the result and what makes up the calculation. If I have made a mistake in any of the formulae, or their target address, you should be able to work out how much to alter the + or - column offset to give the result you want. We could limit the event being triggered to a range of rows 2 to 550 if that is required by entering the following line of code before the Application.EnableEvents = False line If Target.Row < 2 Or Target.Row 550 Then Exit Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Dear Roger,
Great thanks for your support and further help it really works :-). I have 1 more problem to achieve is to make this event macro with my combined cells. Sorry not warning you on time for my combined cells that Cells I-J-K with adress on bar I and M-N-O with adress M combined to eachother. Is it possible to make this macro works with these combinations. Best Regards Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha Many apologies. I added the line to test for columns outside the range 9 to 16 as an afterthought, and this is clearly in the wrong place. It needs to come before Application.EnableEvents = False, otherwise you could exit the sub without switching this back on again. The correct order should be mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Application.EnableEvents = False Select Case mcol -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Reha There is no need to use absolute addressing. Try inserting the following code into your worksheet, and it will work for all rows in the sheet Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long Application.EnableEvents = False mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 3) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 3) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub I have put a rem statements after each Case, so you can see which column triggers the event and which column gets the result and what makes up the calculation. If I have made a mistake in any of the formulae, or their target address, you should be able to work out how much to alter the + or - column offset to give the result you want. We could limit the event being triggered to a range of rows 2 to 550 if that is required by entering the following line of code before the Application.EnableEvents = False line If Target.Row < 2 Or Target.Row 550 Then Exit Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi Reha
I'm sorry, but I don't understand what you mean by my combined cells that Cells I-J-K with address on bar I and M-N-O with address M combined to each other. Could you explain more fully what it is that you want? -- Regards Roger Govier "Reha" wrote in message ... Dear Roger, Great thanks for your support and further help it really works :-). I have 1 more problem to achieve is to make this event macro with my combined cells. Sorry not warning you on time for my combined cells that Cells I-J-K with adress on bar I and M-N-O with adress M combined to eachother. Is it possible to make this macro works with these combinations. Best Regards Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha Many apologies. I added the line to test for columns outside the range 9 to 16 as an afterthought, and this is clearly in the wrong place. It needs to come before Application.EnableEvents = False, otherwise you could exit the sub without switching this back on again. The correct order should be mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Application.EnableEvents = False Select Case mcol -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Reha There is no need to use absolute addressing. Try inserting the following code into your worksheet, and it will work for all rows in the sheet Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long Application.EnableEvents = False mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 3) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 3) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub I have put a rem statements after each Case, so you can see which column triggers the event and which column gets the result and what makes up the calculation. If I have made a mistake in any of the formulae, or their target address, you should be able to work out how much to alter the + or - column offset to give the result you want. We could limit the event being triggered to a range of rows 2 to 550 if that is required by entering the following line of code before the Application.EnableEvents = False line If Target.Row < 2 Or Target.Row 550 Then Exit Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi Roger,
Sorry for my terrible English that I could not use right expressions sometimes. First I tested your macro on an empty workbook and it worked great but when I applied it to my worksheet which has some concatenated cells applied by your macro did not work. Then I altered the target adresses to the adresses of concatenated cells with "+" & "-" as you suggested in your mail but not succeeded even. I was mistaken to write you "Adress on Bar" and correction should be "Name Box" under file menu so I-J-K columns adres seemed as I and M-N-O as M. So columns H-I & L are contiguous to eachother and I altered "case 9" to "case 8" (by the way I have also 1 more contiguous cells B & C to alter case from 9 to 8) then "Target.Offset(0, 3)=" to "Target.Offset(0, 1)=" but I could not see the sum of "H * I" in cell "L". That' s why I wonder if it works with contiguous cells or I wrote wrong target adresses. Big thanks Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha I'm sorry, but I don't understand what you mean by my combined cells that Cells I-J-K with address on bar I and M-N-O with address M combined to each other. Could you explain more fully what it is that you want? -- Regards Roger Govier "Reha" wrote in message ... Dear Roger, Great thanks for your support and further help it really works :-). I have 1 more problem to achieve is to make this event macro with my combined cells. Sorry not warning you on time for my combined cells that Cells I-J-K with adress on bar I and M-N-O with adress M combined to eachother. Is it possible to make this macro works with these combinations. Best Regards Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha Many apologies. I added the line to test for columns outside the range 9 to 16 as an afterthought, and this is clearly in the wrong place. It needs to come before Application.EnableEvents = False, otherwise you could exit the sub without switching this back on again. The correct order should be mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Application.EnableEvents = False Select Case mcol -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Reha There is no need to use absolute addressing. Try inserting the following code into your worksheet, and it will work for all rows in the sheet Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long Application.EnableEvents = False mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 3) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 3) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub I have put a rem statements after each Case, so you can see which column triggers the event and which column gets the result and what makes up the calculation. If I have made a mistake in any of the formulae, or their target address, you should be able to work out how much to alter the + or - column offset to give the result you want. We could limit the event being triggered to a range of rows 2 to 550 if that is required by entering the following line of code before the Application.EnableEvents = False line If Target.Row < 2 Or Target.Row 550 Then Exit Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi Reha
Sorry for my terrible English Please do not apologise. Your English is excellent. I think you mean that you have Merged columns IJK and columns MNO If that is the case, then this modified code should work Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long If Target.Row < 2 Or Target.Row 30 Then Exit Sub Application.EnableEvents = False mcol = Target.Column Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 1) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 1) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, Sorry for my terrible English that I could not use right expressions sometimes. First I tested your macro on an empty workbook and it worked great but when I applied it to my worksheet which has some concatenated cells applied by your macro did not work. Then I altered the target adresses to the adresses of concatenated cells with "+" & "-" as you suggested in your mail but not succeeded even. I was mistaken to write you "Adress on Bar" and correction should be "Name Box" under file menu so I-J-K columns adres seemed as I and M-N-O as M. So columns H-I & L are contiguous to eachother and I altered "case 9" to "case 8" (by the way I have also 1 more contiguous cells B & C to alter case from 9 to 8) then "Target.Offset(0, 3)=" to "Target.Offset(0, 1)=" but I could not see the sum of "H * I" in cell "L". That' s why I wonder if it works with contiguous cells or I wrote wrong target adresses. Big thanks Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha I'm sorry, but I don't understand what you mean by my combined cells that Cells I-J-K with address on bar I and M-N-O with address M combined to each other. Could you explain more fully what it is that you want? -- Regards Roger Govier "Reha" wrote in message ... Dear Roger, Great thanks for your support and further help it really works :-). I have 1 more problem to achieve is to make this event macro with my combined cells. Sorry not warning you on time for my combined cells that Cells I-J-K with adress on bar I and M-N-O with adress M combined to eachother. Is it possible to make this macro works with these combinations. Best Regards Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha Many apologies. I added the line to test for columns outside the range 9 to 16 as an afterthought, and this is clearly in the wrong place. It needs to come before Application.EnableEvents = False, otherwise you could exit the sub without switching this back on again. The correct order should be mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Application.EnableEvents = False Select Case mcol -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Reha There is no need to use absolute addressing. Try inserting the following code into your worksheet, and it will work for all rows in the sheet Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long Application.EnableEvents = False mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 3) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 3) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub I have put a rem statements after each Case, so you can see which column triggers the event and which column gets the result and what makes up the calculation. If I have made a mistake in any of the formulae, or their target address, you should be able to work out how much to alter the + or - column offset to give the result you want. We could limit the event being triggered to a range of rows 2 to 550 if that is required by entering the following line of code before the Application.EnableEvents = False line If Target.Row < 2 Or Target.Row 550 Then Exit Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi Roger again,
You are totally right that the accurate word should be merged cells. I copied the macro and working excellent. One more big thanks for all your support. Regards Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha Sorry for my terrible English Please do not apologise. Your English is excellent. I think you mean that you have Merged columns IJK and columns MNO If that is the case, then this modified code should work Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long If Target.Row < 2 Or Target.Row 30 Then Exit Sub Application.EnableEvents = False mcol = Target.Column Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 1) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 1) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, Sorry for my terrible English that I could not use right expressions sometimes. First I tested your macro on an empty workbook and it worked great but when I applied it to my worksheet which has some concatenated cells applied by your macro did not work. Then I altered the target adresses to the adresses of concatenated cells with "+" & "-" as you suggested in your mail but not succeeded even. I was mistaken to write you "Adress on Bar" and correction should be "Name Box" under file menu so I-J-K columns adres seemed as I and M-N-O as M. So columns H-I & L are contiguous to eachother and I altered "case 9" to "case 8" (by the way I have also 1 more contiguous cells B & C to alter case from 9 to 8) then "Target.Offset(0, 3)=" to "Target.Offset(0, 1)=" but I could not see the sum of "H * I" in cell "L". That' s why I wonder if it works with contiguous cells or I wrote wrong target adresses. Big thanks Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha I'm sorry, but I don't understand what you mean by my combined cells that Cells I-J-K with address on bar I and M-N-O with address M combined to each other. Could you explain more fully what it is that you want? -- Regards Roger Govier "Reha" wrote in message ... Dear Roger, Great thanks for your support and further help it really works :-). I have 1 more problem to achieve is to make this event macro with my combined cells. Sorry not warning you on time for my combined cells that Cells I-J-K with adress on bar I and M-N-O with adress M combined to eachother. Is it possible to make this macro works with these combinations. Best Regards Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha Many apologies. I added the line to test for columns outside the range 9 to 16 as an afterthought, and this is clearly in the wrong place. It needs to come before Application.EnableEvents = False, otherwise you could exit the sub without switching this back on again. The correct order should be mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Application.EnableEvents = False Select Case mcol -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Reha There is no need to use absolute addressing. Try inserting the following code into your worksheet, and it will work for all rows in the sheet Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long Application.EnableEvents = False mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 3) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 3) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub I have put a rem statements after each Case, so you can see which column triggers the event and which column gets the result and what makes up the calculation. If I have made a mistake in any of the formulae, or their target address, you should be able to work out how much to alter the + or - column offset to give the result you want. We could limit the event being triggered to a range of rows 2 to 550 if that is required by entering the following line of code before the Application.EnableEvents = False line If Target.Row < 2 Or Target.Row 550 Then Exit Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
Dependant or Refenced Cells Calculations
Hi Reha
You're very welcome and thanks for the feedback letting me know that it all worked out for you. -- Regards Roger Govier "Reha" wrote in message ... Hi Roger again, You are totally right that the accurate word should be merged cells. I copied the macro and working excellent. One more big thanks for all your support. Regards Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha Sorry for my terrible English Please do not apologise. Your English is excellent. I think you mean that you have Merged columns IJK and columns MNO If that is the case, then this modified code should work Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long If Target.Row < 2 Or Target.Row 30 Then Exit Sub Application.EnableEvents = False mcol = Target.Column Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 1) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 1) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, Sorry for my terrible English that I could not use right expressions sometimes. First I tested your macro on an empty workbook and it worked great but when I applied it to my worksheet which has some concatenated cells applied by your macro did not work. Then I altered the target adresses to the adresses of concatenated cells with "+" & "-" as you suggested in your mail but not succeeded even. I was mistaken to write you "Adress on Bar" and correction should be "Name Box" under file menu so I-J-K columns adres seemed as I and M-N-O as M. So columns H-I & L are contiguous to eachother and I altered "case 9" to "case 8" (by the way I have also 1 more contiguous cells B & C to alter case from 9 to 8) then "Target.Offset(0, 3)=" to "Target.Offset(0, 1)=" but I could not see the sum of "H * I" in cell "L". That' s why I wonder if it works with contiguous cells or I wrote wrong target adresses. Big thanks Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha I'm sorry, but I don't understand what you mean by my combined cells that Cells I-J-K with address on bar I and M-N-O with address M combined to each other. Could you explain more fully what it is that you want? -- Regards Roger Govier "Reha" wrote in message ... Dear Roger, Great thanks for your support and further help it really works :-). I have 1 more problem to achieve is to make this event macro with my combined cells. Sorry not warning you on time for my combined cells that Cells I-J-K with adress on bar I and M-N-O with adress M combined to eachother. Is it possible to make this macro works with these combinations. Best Regards Reha "Roger Govier" , haber iletisinde şunları ... Hi Reha Many apologies. I added the line to test for columns outside the range 9 to 16 as an afterthought, and this is clearly in the wrong place. It needs to come before Application.EnableEvents = False, otherwise you could exit the sub without switching this back on again. The correct order should be mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Application.EnableEvents = False Select Case mcol -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Reha There is no need to use absolute addressing. Try inserting the following code into your worksheet, and it will work for all rows in the sheet Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim mcol As Long Application.EnableEvents = False mcol = Target.Column If mcol <9 Or mcol16 Then Exit Sub Select Case mcol Case 9 ' column I Col L = H*I Target.Offset(0, 3) = Target.Offset(0, -1).Value * Target.Value Case 12 ' column L Col I = L/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -4).Value Case 13 ' column M Col P = H*M Target.Offset(0, 3) = Target.Offset(0, -5).Value * Target.Value Case 16 ' column P Col M = P/H Target.Offset(0, -3) = Target.Value / Target.Offset(0, -8) Case Else End Select Application.EnableEvents = True End Sub I have put a rem statements after each Case, so you can see which column triggers the event and which column gets the result and what makes up the calculation. If I have made a mistake in any of the formulae, or their target address, you should be able to work out how much to alter the + or - column offset to give the result you want. We could limit the event being triggered to a range of rows 2 to 550 if that is required by entering the following line of code before the Application.EnableEvents = False line If Target.Row < 2 Or Target.Row 550 Then Exit Sub -- Regards Roger Govier "Reha" wrote in message ... Hi Roger, I think I could not explain the situation well in my previous mail and decide to write one more with explanotions: The active 8 columns of my table a Column H: I enter the exchange rates Column I: for foreign currency of my debts Column L: for domestic currency of my debts Column M: foreign currency for my payments Column P: domestic currency for my payments Column R: balance for foreign currency Column S: balance for domestic currency I sometimes use the exchange rate if I would like to know how much encountered to foreign or domestic currency upon the variety of debts & payments done but I usually entered them manually so as to use an event macro than formulas on table. My table really works with the event macro down but the problem is with the length of my table consist of 550 rows does not let my event macro run cause of exceeding 64 K. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" Application.EnableEvents = True End Sub I searched for different event macro examples having same kind of calculations dependant to columns but could not adobt them to mine. Event macro should make calculations not adressing definite cells but targeted columns since I enter exchange rate when really needed and applicable for all rows of my table. Kindest thanks for your support Reha "Roger Govier" , haber iletisinde şunları ... Hi I think you need 5 columns for this to work, not 3. Change column letters to suit your needs. I used the following H Rate I Local J Foreign K Calc. Local L Calc. Foreign H, and I or J are used for Input. K and L are calculated fields with the following formulae K2 =IF(H2="","",IF(J2="",I2,J2/H2)) L2 =IF(H2="","",IF(I2="",J2,I2*H2)) -- Regards Roger Govier "Reha" wrote in message ... Hi, I have sheet of 550 rows and I should find a way to calculate between the cells referenced eachother. When I write formulas to each rows sheet warn me for circular reference and reates a big problem for me. The details: Columns "H"(rate of Currency) "I"(Currency Amount) "L"(Amount from Local Currency) Formula No formula-rate only =($L)/($H) =($H)*($I) So Column "I" and "L" dependant to eachother and swap the formula due to the parameters entered. After putting the current currency rate to Column "H" as a constant, Column "I" should apply the formula if I enter the Local Amount to Column "L" or Column "L" automatically calculate the Total (as in the formula) if I enter an amount to Column "I". I used an event macro (Worksheet_SelectionChange) but dissapointed when I wrote formulas for 550 rows and give me the warning of "Too Large Procedure" . I should use 4 steps for each row and became too much when I wrote for 550 displayed the first 4 lines below: If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula = "=$H$13*$I$13" If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula = "=$L$13/$H$13" If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula = "=$H$13*$M$13" If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula = "=$P$13/$H$13" By the way my rows are between 13 and 563! Then I found another from groups written for another kind of circular reference solution but could not succeed to improve for my required formulas which is pasted below: =IF(A1, C1+A1, IF(B1, C1-B1, C1)) Event Macro: Modify the event macro below to read: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cumCell As Range If Not Intersect(Target, Range("A:B")) Is Nothing Then Set cumCell = Range("C" & Target.Row) Application.EnableEvents = False With Target If Left(.Address,2) = "$A" Then cumCell = cumCell + .Value .Offset(0, 1).ClearContents ElseIf Left(.Address,2) = "$B" Then cumCell = cumCell - .Value .Offset(0, -1).ClearContents Else MsgBox "Select either Column A or B, not both!" End If End With Application.EnableEvents = True End If End Sub Thanks indeed for your help to recover my sheet via VBA to use these formulas. Reha |
| All times are GMT +1. The time now is 03:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com