Discussion :

[Aero31]

Bonjour à tous et à toutes,
Je poste ici une demande d'aide car je ne parvient pas à avancer depuis quelques jours (je me considère toujours débutant car je ne travaille pas dans ce domaine mais j'aime essayer de développer des codes qui peuvent m'aider par ailleurs)...

Voici ce que j'essaie de réaliser :

Création d'un code VBA qui se déclenchera en cliquant sur un bouton dans une feuille excel (fonction GenBookingForecast()).
Le but est à partir des données contenues dans la table "T_DataBase" de l'onglet "DataBase" mettre les informations dans la table "T_Booking_Forecast".
Si la première ligne de "T_Booking_Forecast" est vide alors on démarre de celle-ci.

Il y a donc un onglet "Trans. Booking Forecast" qui contient le tableau "T_Booking_Forecast".
Dans ce tableau "T_Booking_Forecast" il faudra remplir chaque ligne et/ou mettre à jour chacune d'entre elle à chaque fois que l'on cliquera sur ce bouton.

Au début, le tableau "T_Booking_Forecast" peut contenir une ligne vide comme indiqué plus haut. Sinon, il contient des lignes non vides.

Si le tableau est vide, ce qui signifie que la première ligne du tableau est la ligne des titres des colonnes et la suivante est celle des données et ici donc vide, alors on commence à parcourir chaque colonne de cette ligne vide et on la remplie suivant les règles mentionnées plus bas.
Sinon, on supprime les lignes pour lequelles la valeur dans la colonne "OPP ID" du tableau "T_Booking_Forecast" n'existe plus dans la colonne "UID" du tableau "T_DataBase" et on mets à jour toutes les lignes concervées et on ajoute toutes les nouvelles suivat les règles ci-dessous/

Le règles sont : (Voici ce qui est attendu pour chaque colonne de la table "T_Booking_Forecast")
"Year" = à partir de la table "T_DataBase" on prend la date qui se trouve dans la colonne "Start Date" et on recherche l'année au format aaaa (la cellule est au format jj/mm/aaaa) et on la rentre dans la cellule correspondante de la table "T_Booking_Forecast"
"QTr" = à partir de la table "T_DataBase" on prend la date qui se trouve dans la colonne "Start Date" et on recherche son Quarter (JFM = si mois est janvier, février ou mars et on ajoute les deux dernier digits de l'année; AMJ = si mois est avril, mai ou juin et on ajoute les deux dernier digits de l'année; JAS = si mois est juillet, aout ou septembre et on ajoute les deux dernier digits de l'année ; OND = si mois est octobre, novembre ou décembre et on ajoute les deux dernier digits de l'année)
"Vertical" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Vertical"
"OPP ID" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "UID"
"Opportunity Name" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Opportunity Name"
"Account Name" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Customer Name"
"Opp Type" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "EE EN NN"
"SOW/PO($ K)" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "TCV" et on recherche les premiers chiffres (numéros) que l'on trouve dans la cellule en partant de la gauche. Si le caractère suivant est aussi un chiffre on le conserve, sinon on arrête et on renvoie tous les chiffres qui ont été trouvés.
exemple si tu trouve "USD 200" alors tu dois retourner 200. Si tu trouves EUR 200 / 230 USD" alors 200 est reporté/retrouné. si tu as "230" alors tu retournes 200 si tu trouves "xdgfefvsdh 340 fzdfzf 456 fzdfzf 333) tu renvoies 340.
Ce ne sont que des exemples, le but est que tu dois coimprendre que dès que tu trouves un chiffre, tu le conserves, tu passes au caractère suivant, si c'est un chiffre, tu le gardes etc juqu'à que ce ne soit plus un chiffre. Ainsi la suite de chiffre que tu as trouvée doit être ce que tu dois copier dans la cellule correspondante.

"Account Manager" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "AM"
"Team" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "BUM Name"
"Geo" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Geo"
"Cross-sell" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Cross Sell"
"SOT/NON-SOT" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "SOT NON SOT"
"Engg/Digital" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Eng Digital"
"Stage" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Excalibur Stage"
"Closure month" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Close Date" et on ne recopie que le mois mm + "'" + les deux derniers digits de l'année "aa"
"Remarks" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Comment"
"OB Status" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Confidence level"
"Expected Closure Date" = à partir de la table "T_DataBase" on prend la valeur qui se trouve dans la colonne "Close Date"


Attention, pour toutes les cellules où on recherche/trouve une date, l'information saisie est de la forme jj/mm/aaa (cellule formatée au format date) (exemple : 01/01/2024 = 01 Ja (janvier) 2024)

Voici le code ou j'en suis :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
Sub GenBookingForecast()
    Dim wsDatabase As Worksheet
    Dim wsForecast As Worksheet
    Dim tblDatabase As ListObject
    Dim tblForecast As ListObject
    Dim rngDatabase As ListRow
    Dim rngForecast As ListRow
    Dim dictUID As Object
    Dim rowIndex As Long
    Dim currentYear As Variant ' Utilisez Variant pour permettre de gérer différents types
    Dim quarter As String
    Dim tcvValue As String
    Dim lastRow As ListRow
    Dim startDateColumnIndex As Integer
 
    ' Initialisation des feuilles et tableaux
    Set wsDatabase = ThisWorkbook.Sheets("DataBase")
    Set wsForecast = ThisWorkbook.Sheets("Trans. Booking Forecast")
    Set tblDatabase = wsDatabase.ListObjects("T_DataBase")
    Set tblForecast = wsForecast.ListObjects("T_Booking_Forecast")
    Set dictUID = CreateObject("Scripting.Dictionary")
 
    ' Charger les UIDs existants de T_DataBase dans le dictionnaire
    For Each rngDatabase In tblDatabase.ListRows
        If Not IsEmpty(rngDatabase.Range.Columns("UID").value) Then
            dictUID(CStr(rngDatabase.Range.Columns("UID").value)) = rngDatabase.Range
        End If
    Next rngDatabase
 
    ' Supprimer les lignes de T_Booking_Forecast dont les UIDs n'existent plus
    For rowIndex = tblForecast.ListRows.Count To 1 Step -1
        Dim uidValue As Variant
        uidValue = tblForecast.ListRows(rowIndex).Range.Columns("UID").value
        If Not IsEmpty(uidValue) And Not dictUID.Exists(CStr(uidValue)) Then
            tblForecast.ListRows(rowIndex).Delete
        End If
    Next rowIndex
 
    ' Déterminer l'index de la colonne "Start Date" (26e colonne)
    startDateColumnIndex = 26  ' Assurez-vous que c'est la bonne colonne
 
    ' Mettre à jour ou ajouter des lignes dans T_Booking_Forecast
    For Each rngDatabase In tblDatabase.ListRows
        Dim existingRow As ListRow
        Set existingRow = Nothing
 
        ' Rechercher si l'UID existe déjà dans T_Booking_Forecast
        For Each rngForecast In tblForecast.ListRows
            If CStr(rngForecast.Range.Columns("UID").value) = CStr(rngDatabase.Range.Columns("UID").value) Then
                Set existingRow = rngForecast
                Exit For
            End If
        Next rngForecast
 
        ' Déterminer la ligne cible (nouvelle ou existante)
        If existingRow Is Nothing Then
            Set lastRow = tblForecast.ListRows.Add
            Set existingRow = lastRow
        End If
 
        ' Remplir les colonnes selon les règles
        With rngDatabase.Range
            ' Vérifier la validité de la date pour éviter l'erreur
            If Not IsError(.Columns(startDateColumnIndex).value) And Not IsEmpty(.Columns(startDateColumnIndex).value) Then
                Debug.Print "Valeur brute de Start Date : " & .Columns(startDateColumnIndex).value
                On Error Resume Next
                currentYear = CDate(.Columns(startDateColumnIndex).value) ' Conversion explicite en Date
                On Error GoTo 0
 
                If IsDate(currentYear) Then
                    Debug.Print "Date valide : " & currentYear
                    ' Passer l'argument sous forme de Date
                    quarter = GetQuarter(CDate(currentYear)) ' Assurez-vous que GetQuarter accepte bien un Date
                    ' Extraire les 2 derniers chiffres de l'année pour l'affecter à currentYear
                    currentYear = Right(CStr(year(currentYear)), 2)
                Else
                    Debug.Print "Start Date invalide"
                    currentYear = "00" ' Remettre à "00" si la date n'est pas valide (comme si l'année était 2000)
                    quarter = ""
                End If
            Else
                currentYear = "00" ' Assurez-vous que currentYear soit "00" si la date est vide ou invalide
                quarter = ""
            End If
 
            ' Extraire les TCV (si applicable)
            tcvValue = ExtractFirstNumber(.Columns("TCV").value)
        End With
 
        ' Remplir les colonnes cibles
        With existingRow.Range
            ' Si currentYear est valide, on met l'année (deux derniers chiffres)
            If currentYear <> "" Then
                .Columns("Year").value = currentYear ' Remplir avec "00" ou l'année
            Else
                .Columns("Year").value = "" ' Laissez la cellule vide si aucune année valide
            End If
 
            If quarter <> "" Then
                .Columns("QTr").value = quarter ' Ajouter le trimestre sous le bon format
            Else
                .Columns("QTr").value = "" ' Laissez la cellule vide si aucun trimestre valide
            End If
 
            .Columns("Vertical").value = rngDatabase.Range.Columns("Vertical").value
            .Columns("OPP ID").value = rngDatabase.Range.Columns("UID").value
            .Columns("Opportunity Name").value = rngDatabase.Range.Columns("Opportunity Name").value
            .Columns("Account Name").value = rngDatabase.Range.Columns("Customer Name").value
            .Columns("Opp Type").value = rngDatabase.Range.Columns("EE EN NN").value
        End With
 
    Next rngDatabase
End Sub
 
' Fonction pour extraire le premier nombre de la chaîne TCV
Function ExtractFirstNumber(ByVal text As String) As String
    Dim match As Object
    Set match = Nothing
    Set match = CreateObject("VBScript.RegExp")
    match.IgnoreCase = True
    match.Global = False
    match.Pattern = "\d+(\.\d{1,2})?"
    If match.TEST(text) Then
        ExtractFirstNumber = match.Execute(text)(0)
    Else
        ExtractFirstNumber = ""
    End If
End Function
 
' Fonction GetQuarter pour déterminer le trimestre basé sur l'année et le mois
Function GetQuarter(dateValue As Date) As String
    Dim month As Integer
    Dim year As Integer
    Dim quarter As String
 
    month = month(dateValue)
    year = year(dateValue)
 
    ' Logique pour déterminer le trimestre en fonction du mois
    Select Case month
        Case 1 To 3
            quarter = "JFM" & Right(CStr(year), 2) ' Janvier, Février, Mars
        Case 4 To 6
            quarter = "AMJ" & Right(CStr(year), 2) ' Avril, Mai, Juin
        Case 7 To 9
            quarter = "JAS" & Right(CStr(year), 2) ' Juillet, Août, Septembre
        Case 10 To 12
            quarter = "OND" & Right(CStr(year), 2) ' Octobre, Novembre, Décembre
        Case Else
            quarter = ""
    End Select
 
    GetQuarter = quarter
End Function

Titres des colonnes de la table T_DataBase :

UID Customer Customer Code Customer Name Customer Group ERS NOERS Vertical Vertical Sales Geo BUM Name TL Name AM EE EN NN Country Project Services Type Sub Type Confidence level Excalibur Salesforce ID Opportunity Name Comment Excalibur Stage Stage Details Open Date Start Date Close Date TCV Currency Cross Sell SOT NON SOT Eng Digital

Titres des colonnes de la table T_Booking_Forecast :

Year QTr Vertical OPP ID Opportunity Name Account Name Opp Type "SOW/PO
($ K)" Account Manager Team Geo Cross-sell SOT/NON-SOT Engg/Digital Stage Closure month Remarks OB Status Expected Closure Date

Mon souci :
J'ai une erreur d'execution 13 : incompatible type à la ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

                .Columns("Year").value = currentYear ' Remplir avec "00" ou l'année

Les traces que j'avais relevées sont :

La colonne 'Start Date' a été trouvée à l'indice 26
Adresse de la cellule Start Date pour UID : $Z$5
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$6
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$7
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$8
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$9
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$10
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$11
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$12
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$13
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$14
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$15
Start Date est vide ou une erreur dans la cellule.
Adresse de la cellule Start Date pour UID : $Z$16
Start Date : 16/01/2025
Initialisation terminée.
Nombre de lignes dans T_DataBase : 12
Nombre de lignes dans T_Booking_Forecast : 1
Mise à jour de la ligne existante pour UID :
Traitement de la ligne avec UID :

Merci d'avance pour votre aide.
Au palsir de vous lire et bonne journée.
Cordialement,
Aero31

[Duanra21]

Bonjour,

Je n'ai pas vraiment lu tous ton message

mais pourquoi

Columns("Year").value = currentYear

et non

Range("Year").value = currentYear

?

[Aero31]

Bonjour Duanra21,

Je travaille avec un tableau structuré (ListObject) T_DataBase et T_Booking_Forecast, .Columns("...") permet de faire référence à une colonne spécifique par son nom dans ce tableau structuré.
Cela garantit que je cible précisément la colonne du tableau, peu importe où elle se trouve dans la feuille.
Donc, je peux modifier la position de mes colonnes dans mon tableau, le code fonctionnera toujours (utile quand on fait évoluer un tableau en ajoutant, supprimant des colonnes et.
Plus facile pour maintenir le code et le faire évoluer...
Cordialement

[deedolith]

Salut,

Est-tu obligé de passer par VBA ?
S'il s'agit d'une transformation de données, Power Query peut faire le job.
Le coté VBA se résumeras alors à déclencher la mise à jour des requêtes.

[Aero31]

Salut deedolith
Surement, mais je ne maitrise absolument pas Power Query ...
Du coup, j'avance doucement avec le peu que je sais
En tous les cas merci pour l'idée... ça doit être mieux avec Power Query en effet...
Cordialement

[Duanra21]

Si c'est un tableau structuré c'est pas plus


Range("Tableau1[year]") = currentYear