1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
| 'revoir les dimentions
Sub Delta_Other_Currency_Pos(xlsheet As Worksheet, ByVal Perimetre As String, ByVal Tableau As String)
'Defintion des variables
Dim Matrice(), TabCurrency(), Mat(), TabPeriMonnaie(), res(1, 6), resT(), SresT(), Tabl()
Dim i As Long, j As Long
Dim dico As Dictionary
Dim AllRange As Range, MyRange As Range
Dim NbL As Long
Dim Val As Double
Dim Perimetre_Nom_Feuille As String
'On determine NbL
NbL = xlsheet.Range("D" & Rows.Count).End(xlUp).Row
'definition du range
ThisWorkbook.Worksheets("Delta").Activate
With ActiveSheet
Set AllRange = .Range(Range("D11"), Range("D" & NbL))
End With
AllRange.Select
'on recupere le tableau des devises non travaillées
For Each MyRange In AllRange
Select Case Perimetre
Case Is = "LQB TRD"
If (Not MyRange.Value = "EUR" And Not MyRange.Value = "JPY") And Not MyRange.Value = "USD" Then
i = i + 1
ReDim Preserve TabCurrency(i)
TabCurrency(i) = MyRange.Value
End If
Case Is = "LDN CF 70805"
If Not MyRange.Value = "EUR" And Not MyRange.Value = "USD" Then
i = i + 1
ReDim Preserve TabCurrency(i)
TabCurrency(i) = MyRange.Value
End If
End Select
Next MyRange
'Copie du range dans la matrice
ThisWorkbook.Worksheets("Parametrage").Activate
Matrice = ActiveSheet.Range("Mat_Transition").Value
For i = 1 To UBound(TabCurrency)
TabPeriMonnaie = Fonctions.getP_Ligne(TabCurrency(i), xlsheet, Perimetre)
ThisWorkbook.Worksheets("Delta").Activate
'Si la premiere ligne vide alors vide tt court
If Not IsEmpty(TabPeriMonnaie(1)) Then
Mat = ActiveSheet.Range(Range("M" & TabPeriMonnaie(1)), Range("AS" & TabPeriMonnaie(2))).Value
Else:
Exit Sub
End If
'certain tableau vide
'Erase resT
resT = Application.WorksheetFunction.MMult(Mat, Matrice)
For j = 1 To UBound(resT, 1)
'on somme les colonnes
Val = Application.WorksheetFunction.Sum(Application.WorksheetFunction.Index(resT, j))
ReDim Preserve Tabl(1, j)
Tabl(1, j) = Val
Next j
Next i
'transpose
Mat = Application.WorksheetFunction.Transpose(Tabl)
'copier tableau dans un range
If Perimetre = "LQB TRD" Then
Perimetre_Nom_Feuille = "DB LQB"
Else:
Perimetre_Nom_Feuille = "DB Agencies"
End If
ThisWorkbook.Worksheets(Perimetre_Nom_Feuille).Activate
ActiveSheet.Range(Tableau) = Mat
End Sub |
Partager