VBA - Formule MAX Matricielle sur Date avec plusieurs conditions
Bonjour,
J'ai mis en place une macro qui consolide plusieurs fichiers dans une base de données. La volumétrie finale sur et d'environ 20 000 lignes et 40 colonnes.
Je bloque sur l'ajout d'une formule, au niveau de ma macro, permettant de retrouver pour chaque ligne la date qui la précède selon 3 critères (site, unité et tâche identique).
J'ai intégré dans ma macro la formule matricielle suivante que je copie / colle sur l'ensemble des lignes de la base de données :
{=MAX(SI(($E:$E<$E16)*($A:$A=$A16)*($B:$B=$B16)*($F:$F=$F16);$E:$E))}
Cela fonctionne presque(s'il n'y a pas de date précédant j'aimerais qu'il saisisse la date du jour) lorsque la volumétrie est faible, mais une fois la base de données complète réalisée le fichier plante.
J'aimerai pouvoir trouver un moyen de réaliser l'équivalent de cette formule avec une / des variables tableaux (afin d'optimiser les temps de calcul). J'ai réalisé le code suivant mais il ne me renvoie des données vides. Je pense que le problème vient des dates mais je ne comprend pas comment le résoudre :
Sub MAX_IF()
'Définition des variables de configuration
Dim BoEcran As Boolean, BoBarre As Boolean, BoEvent As Boolean, BoSaut As Boolean
Dim iCalcul As Integer
'Enregistrement des configuration initiale pour l'ensemble de la macro
optimization = True
BoEcran = Application.ScreenUpdating
BoBarre = Application.DisplayStatusBar
iCalcul = Application.Calculation
BoEvent = Application.EnableEvents
BoSaut = ActiveSheet.DisplayPageBreaks
'Désactivation des configurations pour optimiser le calcul dans la macro
Application.ScreenUpdating = False 'une fois par macro
Application.DisplayStatusBar = False 'une fois par macro
Application.Calculation = xlCalculationManual 'pour chaque onglet
Application.EnableEvents = False 'pour chaque fichier
ActiveSheet.DisplayPageBreaks = False 'pour chaque onglet
'Définition des variables de calcul
Dim tabb1() As Variant
Dim tabb2() As Variant
Dim i As Long, j As Long, derniere_ligne As Long, derniere_ligne_table As Long
Dim max As Variant
Dim intTypeDeVar As Integer
'Définition des variables
Set w0 = ThisWorkbook
Set s_bdd_stocks = w0.Sheets("BDD_ACTIVITE")
'Création des tableaux tabb1 et tabb2
derniere_ligne = s_bdd_stocks.Range("A1").End(xlDown).Row
derniere_ligne_table = derniere_ligne - 2
ReDim tabb1(derniere_ligne_table, 4)
ReDim tabb2(derniere_ligne_table, 4)
For i = 2 To derniere_ligne
If IsDate(s_bdd_stocks.Cells(i, 5)) = True And InStr(s_bdd_stocks.Cells(i, 5), "/") Then
s_bdd_stocks.Cells(i, 5) = Format(s_bdd_stocks.Cells(i, 5), "yyyy-mm-dd")
tabb1(i - 2, 0) = s_bdd_stocks.Cells(i, 1) 'Site
tabb1(i - 2, 1) = s_bdd_stocks.Cells(i, 2) 'UG
tabb1(i - 2, 2) = s_bdd_stocks.Cells(i, 6) 'Case
tabb1(i - 2, 3) = CDate(s_bdd_stocks.Cells(i, 5)) 'Jour
tabb2(i - 2, 0) = s_bdd_stocks.Cells(i, 1) 'Site
tabb2(i - 2, 1) = s_bdd_stocks.Cells(i, 2) 'UG
tabb2(i - 2, 2) = s_bdd_stocks.Cells(i, 6) 'Case
tabb2(i - 2, 3) = CDate(s_bdd_stocks.Cells(i, 5)) 'Jour
End If
Next i
'Comparaison des des dates des tableaux tabb1(date)<tabb2(date)
max = ""
For i = 0 To derniere_ligne_table
For j = 0 To derniere_ligne_table
If tabb1(i, 0) = tabb2(j, 0) And tabb1(i, 1) = tabb2(j, 1) And tabb1(i, 2) = tabb2(j, 2) And tabb1(i, 3) > tabb2(j, 3) Then
If IsDate(tabb2(j, 4)) = True And InStr(tabb2(j, 4), "/") Then
tabb2(j, 4) = Format(tabb2(j, 4), "yyyy-mm-dd")
max = tabb2(j, 4).Value
End If
End If
Next j
MsgBox (max)
If max <> "01/01/1900" Then
For k = 2 To derniere_ligne
If tabb1(i, 0) = s_bdd_stocks.Cells(k, 1) And tabb1(i, 1) = s_bdd_stocks.Cells(k, 2) And tabb1(i, 2) = s_bdd_stocks.Cells(k, 6) Then
s_bdd_stocks.Cells(k, 7) = max
End If
Next k
End If
Next i
'Restauration des configuration initiale
ActiveSheet.DisplayPageBreaks = BoSaut
Application.EnableEvents = boevents
Application.Calculation = iCalcul
Application.DisplayStatusBar = BoBarre
Application.ScreenUpdating = BoEcran
End Sub
Le code n'est pas top, je débute :-/
Merci d'avance de votre aide.
Rafik
bonjour
je propose de ne pas faire de macro du tout
Power BI desktop gratuit sait :
- récupérer et fusionner tous tes fichiers de base (d'un clic)
- traiter des millions de lignes
on essaye ?
joindre 2 fichiers de base (mettre seulement 3 colonnes et 100 lignes pour passer sur ce forum)
et joindre le résultat que tu attends
à te relire
bonjour
je propose de ne pas faire de macro du tout
Power BI desktop gratuit sait :
- récupérer et fusionner tous tes fichiers de base (d'un clic)
- traiter des millions de lignes
on essaye ?
joindre 2 fichiers de base (mettre seulement 3 colonnes et 100 lignes pour passer sur ce forum)
et joindre le résultat que tu attends
à te relire
Bonjour jmd,
Merci pour la proposition, malheureusement je dois rester sur du Excel / VBA ... même si la puissance de PowerBI serait une bien meilleure solution.
Une autre idée en VBA ?
bonjour
je propose de ne pas faire de macro du tout
Power BI desktop gratuit sait :
- récupérer et fusionner tous tes fichiers de base (d'un clic)
- traiter des millions de lignes
on essaye ?
joindre 2 fichiers de base (mettre seulement 3 colonnes et 100 lignes pour passer sur ce forum)
et joindre le résultat que tu attends
à te relire
Bonjour jmd,
Merci pour la proposition, malheureusement je dois rester sur du Excel / VBA ...
pourquoi ? il est gratuit
Je sais qu'il est gratuit, je l'utilise à titre personnel.
Malheureusement il n'est pas déployé par la DSI... Une autre solution est en cours de réalisation.
DONC back to VBA
Je sais qu'il est gratuit, je l'utilise à titre personnel.
Malheureusement il n'est pas déployé par la DSI... Une autre solution est en cours de réalisation.
DONC back to VBA
une DSi des années 1980 !
ils autorisent VBA (passoire à virus) mais interdisent les progrès
ça leur permet de garder leur pouvoir
et surtout ils ralentissent les entreprises comme un caillou dans la chaussure
alors que leur rôle est venir te voir tous les mois en disant : "dis, tu as vu, il y a Microsoft Flow, les PowerApps. Est-ce que tu en as besoin ? on va voir ensemble"
ils ont le pied sur le frein au lieu d'enfoncer l'accélérateur
ça me rend fou !
et surtout pas d'intelligence artificielle !
concernant VBA, je passe la parole à d'autres
courage
Je sais qu'il est gratuit, je l'utilise à titre personnel.
Malheureusement il n'est pas déployé par la DSI... Une autre solution est en cours de réalisation.
DONC back to VBA
une DSi des années 1980 !
ils autorisent VBA (passoire à virus) mais interdisent les progrès
ça leur permet de garder leur pouvoir
et surtout ils ralentissent les entreprises comme un caillou dans la chaussure
alors que leur rôle est venir te voir tous les mois en disant : "dis, tu as vu, il y a Microsoft Flow, les PowerApps. Est-ce que tu en as besoin ? on va voir ensemble"
ils ont le pied sur le frein au lieu d'enfoncer l'accélérateur
ça me rend fou !
et surtout pas d'intelligence artificielle !
concernant VBA, je passe la parole à d'autres
courage
Je te remercie pour ton soutient psychologique
Est-ce que d'autres personnes ont des pistes pour ma macro ?