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