TheProdigy
XLDnaute Impliqué
Bonjour tou le monde bonjour les vbasites,
Je voudrais ajouter un autre critère sur "Sell" et "Buy",
1) le critère "Sell"; soit "Sell" soit "Ven".
2) La même chose pour "buy"; soit "buy" soit "Ach"
Le code
Option Explicit
Function fifoval(q As Range, Optional details As String) As Variant
Application.Volatile (True)
Dim i As Integer
Dim qstr As String
Dim pstr As String
Dim cqty As Integer
Dim prc As Double
Dim qty As Integer
Dim ctr As Integer
Dim dstr As String
Dim amt As Double
'Stop
For i = 2 To q.Row - 1
If Cells(i, 1) = Cells(q.Row, 1) Then
Select Case Cells(i, 2)
Case "Buy"
qstr = qstr & Cells(i, 4) & ","
pstr = pstr & Cells(i, 5) & ","
Case "Sell"
qty = Cells(i, 4)
Do While qty > 0
cqty = Val(qstr)
If cqty = 0 Then
fifoval = "Not enough balance"
Exit Function
End If
Select Case True
Case cqty = qty
qstr = Replace(qstr, cqty & ",", "", , 1)
pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
qty = qty - cqty
Case cqty > qty
qstr = Replace(qstr, cqty, cqty - qty, , 1)
qty = 0
Case cqty < qty
qstr = Replace(qstr, cqty & ",", "", , 1)
pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
qty = qty - cqty
Case cqty = 0
fifoval = "Not enough balance"
Exit Function
End Select
ctr = ctr + 1
If ctr > 1000 Then End: Stop
Loop
End Select
End If
Next i
qty = Cells(q.Row, 4)
Do While qty > 0
cqty = Val(qstr)
If cqty = 0 Then
fifoval = "Not enough balance"
Exit Function
End If
prc = Val(pstr)
Select Case True
Case cqty = qty
dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
amt = amt + qty * prc
qstr = Replace(qstr, cqty & ",", "", , 1)
pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
qty = qty - cqty
Case cqty > qty
dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
amt = amt + qty * prc
qstr = Replace(qstr, cqty, cqty - qty, , 1)
qty = 0
Case cqty < qty
dstr = dstr & IIf(dstr = "", "", " + ") & cqty & " * " & prc
amt = amt + cqty * prc
qstr = Replace(qstr, cqty & ",", "", , 1)
pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
qty = qty - cqty
End Select
ctr = ctr + 1
If ctr > 1000 Then End: Stop
Loop
If details = "" Then
fifoval = amt
Else
fifoval = dstr
End If
End Function
Merci beaucoup
Je voudrais ajouter un autre critère sur "Sell" et "Buy",
1) le critère "Sell"; soit "Sell" soit "Ven".
2) La même chose pour "buy"; soit "buy" soit "Ach"
Le code
Option Explicit
Function fifoval(q As Range, Optional details As String) As Variant
Application.Volatile (True)
Dim i As Integer
Dim qstr As String
Dim pstr As String
Dim cqty As Integer
Dim prc As Double
Dim qty As Integer
Dim ctr As Integer
Dim dstr As String
Dim amt As Double
'Stop
For i = 2 To q.Row - 1
If Cells(i, 1) = Cells(q.Row, 1) Then
Select Case Cells(i, 2)
Case "Buy"
qstr = qstr & Cells(i, 4) & ","
pstr = pstr & Cells(i, 5) & ","
Case "Sell"
qty = Cells(i, 4)
Do While qty > 0
cqty = Val(qstr)
If cqty = 0 Then
fifoval = "Not enough balance"
Exit Function
End If
Select Case True
Case cqty = qty
qstr = Replace(qstr, cqty & ",", "", , 1)
pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
qty = qty - cqty
Case cqty > qty
qstr = Replace(qstr, cqty, cqty - qty, , 1)
qty = 0
Case cqty < qty
qstr = Replace(qstr, cqty & ",", "", , 1)
pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
qty = qty - cqty
Case cqty = 0
fifoval = "Not enough balance"
Exit Function
End Select
ctr = ctr + 1
If ctr > 1000 Then End: Stop
Loop
End Select
End If
Next i
qty = Cells(q.Row, 4)
Do While qty > 0
cqty = Val(qstr)
If cqty = 0 Then
fifoval = "Not enough balance"
Exit Function
End If
prc = Val(pstr)
Select Case True
Case cqty = qty
dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
amt = amt + qty * prc
qstr = Replace(qstr, cqty & ",", "", , 1)
pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
qty = qty - cqty
Case cqty > qty
dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
amt = amt + qty * prc
qstr = Replace(qstr, cqty, cqty - qty, , 1)
qty = 0
Case cqty < qty
dstr = dstr & IIf(dstr = "", "", " + ") & cqty & " * " & prc
amt = amt + cqty * prc
qstr = Replace(qstr, cqty & ",", "", , 1)
pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
qty = qty - cqty
End Select
ctr = ctr + 1
If ctr > 1000 Then End: Stop
Loop
If details = "" Then
fifoval = amt
Else
fifoval = dstr
End If
End Function
Merci beaucoup
Dernière édition: