rowIndex = Application.Match(dateValue, wsDestination.Columns(1), 0) + 1 : bug type :…
- User
rowIndex = Application.Match(dateValue, wsDestination.Columns(1), 0) + 1 : bug type :
Option Explicit
Sub CreatePivotTablevvvvvvvvvvvvvvvvvvvvvvv() Dim wsSource As Worksheet Dim wsDestination As Worksheet Dim lastRow As Long Dim i As Long, j As Long Dim dateValue As Date, hourValue As Date Dim dict As Object Dim key As Variant
' Définir les feuilles source et destination Set wsSource = ActiveSheet Set wsDestination = Sheets.Add(After:=ActiveSheet) wsDestination.Name = "Tableau Croisé Dynamique" ' Trouver la dernière ligne des données source lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).row ' Créer un dictionnaire pour stocker les valeurs uniques de date Set dict = CreateObject("Scripting.Dictionary") ' Parcourir les données source et ajouter les dates uniques au dictionnaire For i = 2 To lastRow dateValue = CDate(wsSource.Cells(i, 2).value) If dateValue >= DateSerial(2024, 1, 1) And dateValue <= DateSerial(2024, 1, 31) Then If Not dict.Exists(dateValue) Then dict.Add dateValue, 0 End If End If Next i ' Créer les en-têtes de ligne (dates) wsDestination.Cells(1, 1).value = "Date" i = 2 For Each key In dict.Keys wsDestination.Cells(i, 1).value = key wsDestination.Cells(i, 1).NumberFormat = "dd/mm/yyyy" i = i + 1 Next key ' Créer les en-têtes de colonne (heures) For j = 2 To 25 wsDestination.Cells(1, j).value = Format(TimeSerial(j - 2, 0, 0), "hh:mm") Next j ' Remplir les valeurs dans le tableau croisé dynamique Application.ScreenUpdating = False For i = 2 To lastRow dateValue = CDate(wsSource.Cells(i, 2).value) hourValue = CDate(wsSource.Cells(i, 1).value) If dateValue >= DateSerial(2024, 1, 1) And dateValue <= DateSerial(2024, 1, 31) Then Dim rowIndex As Long rowIndex = Application.Match(dateValue, wsDestination.Columns(1), 0) + 1 Dim colIndex As Long colIndex = Application.Match(Format(hourValue, "hh:mm"), wsDestination.Rows(1), 0) + 1 If rowIndex > 1 And colIndex > 1 Then wsDestination.Cells(rowIndex, colIndex).value = wsSource.Cells(i, 3).value End If End If Next i Application.ScreenUpdating = True ' Formater le tableau With wsDestination.Range(wsDestination.Cells(1, 1), wsDestination.Cells(dict.Count + 1, 25)) .Borders.LineStyle = xlContinuous .Borders.Weight = xlThin .Interior.Color = RGB(220, 220, 220) .Font.Bold = True .HorizontalAlignment = xlCenter End With ' Ajuster la largeur des colonnes wsDestination.Columns.AutoFitEnd Sub
- Assistant
The line where you're trying to find the
rowIndexwithMatchfunction includes a bug:rowIndex = Application.Match(dateValue, wsDestination.Columns(1), 0) + 1Description of the Bug
The issue here is that the
Application.Matchfunction may return an error if the value (dateValue) is not found in the specified range (wsDestination.Columns(1)). In VBA, trying to perform an arithmetic operation (+1) directly on an error value without handling it results in a runtime error (Run-time error '13': Type mismatchorRun-time error '1004': Application-defined or object-defined errordepending on the version of Excel and the specific circumstances).This bug can disrupt the macro, especially if a date in the source sheet does not exist in the destination sheet, a condition which could quite easily occur with data inputs.
Suggested Fix
Before adding 1 to the result of the
Matchfunction, you should check if it returns an error. You can do that by using theIsErrorfunction. Only proceed to add 1 if it is not an error. You might also want to handle the case where the match is not found differently, possibly skipping the current iteration or taking some other action.Here's a corrected version of problematic line:
Dim matchResult As Variant matchResult = Application.Match(dateValue, wsDestination.Columns(1), 0) If Not IsError(matchResult) Then rowIndex = matchResult + 1 Else ' Handle cases where the date is not found. Maybe continue to next iteration or log error ' For instance, you might just use 'Continue For' if within a loop, or set rowIndex to an error code. ' For this example, let's just print to Debug console and skip this iteration Debug.Print "Date not found: " & dateValue GoTo NextIteration End IfAnd make sure the loop that contains it has a label for
NextIterationjust before its end:... NextIteration: Next iRemember, this change requires you to properly handle the situation for when a date or time is not found, depending on your exact needs. For cases where you decide to skip an iteration (
GoTo NextIteration), ensure that the labelNextIteration:is correctly placed at the end of your loop to avoid skipping necessary executions accidentally.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.