Gini88
04-24-2016, 05:42 AM
Hello,
i'm trying to automate calculating differences in exchange rates - FIFO method. The problem is that I don't know how to handle with the situation, where transactions are not equal.
15996
Sub FIFO()
Dim suma_in As Double
Dim suma_out As Double
Dim left_in As Double
Dim left_out As Double
'row indexes
Dim i As Integer
Dim j As Integer
Dim k As Integer
'column indexes
Dim kolumna_in As Integer
Dim kolumna_out As Integer
For i = 2 To Sheets("Arkusz2").Range("C65536").End(xlUp).Row ' goes through the in transactions
left_in = (Sheets("Arkusz2").Range("C" & i).Value) ' calculates how much money is left
j = 2
k = 2
suma_in = (Sheets("Arkusz2").Range("C" & j)) * (Sheets("Arkusz2").Range("A" & j).Value) 'multiplies the amount by exchange rate
kolumna_in = 0
kolumna_out = 1
If left_in >= left_out Then 'checks if in>out
suma_out = Abs(Sheets("Arkusz3").Range("A" & k).Value)
'lists in the next columns the amount of calculated transactions
Sheets("Arkusz2").Range("D" & j).Offset(0, kolumna_in + 1) = suma_out * (Sheets("Arkusz2").Range("A" & j).Value)
Sheets("Arkusz3").Range("C" & k).Value = suma_out * Sheets("Arkusz2").Range("A" & j).Value
left_in = left_in + suma_out
left_out = suma_out - left_in
k = k + 1
kolumna_in = kolumna_in + 1
kolumna_out = kolumna_out + 1
'otherwise calculates analogically
Else
suma_in = Abs(Sheets("Arkusz2").Range("C" & j).Value)
Sheets("Arkusz2").Range("D" & j) = suma_in * (Sheets("Arkusz2").Range("A" & j).Value)
Sheets("Arkusz3").Range("C" & k).Offset(0, kolumna_out + 1) = suma_in * Sheets("Arkusz2").Range("A" & j).Value
left_in = left_in + suma_out
left_out = suma_out - left_in
j = j + 1
kolumna_in = kolumna_in + 1
kolumna_out = kolumna_out + 1
End If
Next i
End Sub
i'm trying to automate calculating differences in exchange rates - FIFO method. The problem is that I don't know how to handle with the situation, where transactions are not equal.
15996
Sub FIFO()
Dim suma_in As Double
Dim suma_out As Double
Dim left_in As Double
Dim left_out As Double
'row indexes
Dim i As Integer
Dim j As Integer
Dim k As Integer
'column indexes
Dim kolumna_in As Integer
Dim kolumna_out As Integer
For i = 2 To Sheets("Arkusz2").Range("C65536").End(xlUp).Row ' goes through the in transactions
left_in = (Sheets("Arkusz2").Range("C" & i).Value) ' calculates how much money is left
j = 2
k = 2
suma_in = (Sheets("Arkusz2").Range("C" & j)) * (Sheets("Arkusz2").Range("A" & j).Value) 'multiplies the amount by exchange rate
kolumna_in = 0
kolumna_out = 1
If left_in >= left_out Then 'checks if in>out
suma_out = Abs(Sheets("Arkusz3").Range("A" & k).Value)
'lists in the next columns the amount of calculated transactions
Sheets("Arkusz2").Range("D" & j).Offset(0, kolumna_in + 1) = suma_out * (Sheets("Arkusz2").Range("A" & j).Value)
Sheets("Arkusz3").Range("C" & k).Value = suma_out * Sheets("Arkusz2").Range("A" & j).Value
left_in = left_in + suma_out
left_out = suma_out - left_in
k = k + 1
kolumna_in = kolumna_in + 1
kolumna_out = kolumna_out + 1
'otherwise calculates analogically
Else
suma_in = Abs(Sheets("Arkusz2").Range("C" & j).Value)
Sheets("Arkusz2").Range("D" & j) = suma_in * (Sheets("Arkusz2").Range("A" & j).Value)
Sheets("Arkusz3").Range("C" & k).Offset(0, kolumna_out + 1) = suma_in * Sheets("Arkusz2").Range("A" & j).Value
left_in = left_in + suma_out
left_out = suma_out - left_in
j = j + 1
kolumna_in = kolumna_in + 1
kolumna_out = kolumna_out + 1
End If
Next i
End Sub