Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0125-preview· OpenAIAi2 WildChat-4.8M (AI2)

rowIndex = Application.Match(dateValue, wsDestination.Columns(1), 0) + 1 : bug type :…

  1. 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.AutoFit
    

    End Sub

  2. Assistant

    The line where you're trying to find the rowIndex with Match function includes a bug:

    rowIndex = Application.Match(dateValue, wsDestination.Columns(1), 0) + 1
    

    Description of the Bug

    The issue here is that the Application.Match function 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 mismatch or Run-time error '1004': Application-defined or object-defined error depending 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 Match function, you should check if it returns an error. You can do that by using the IsError function. 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 If
    

    And make sure the loop that contains it has a label for NextIteration just before its end:

    ...
    NextIteration:
    Next i
    

    Remember, 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 label NextIteration: is correctly placed at the end of your loop to avoid skipping necessary executions accidentally.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.