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

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

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

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

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

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

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

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

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

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

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