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

Sub CreateHourlySummary2() Dim rawDataSheet As Worksheet Dim summarySheet As Worksheet…

  1. User

    Sub CreateHourlySummary2() Dim rawDataSheet As Worksheet Dim summarySheet As Worksheet Dim lastRow As Long Dim i As Long Dim dateVal As Variant Dim hourVal As Integer Dim summaryRow As Variant

    ' Add a new sheet for raw data
    Set rawDataSheet = Worksheets.Add(After:=ActiveSheet)
    rawDataSheet.Name = "Raw Data"
    
    ' Import the CSV file into the raw data sheet
    With rawDataSheet.QueryTables.Add(Connection:="TEXT;" & Application.GetOpenFilename(FileFilter:="CSV Files (.csv),.csv"), Destination:=rawDataSheet.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With
    
    ' Add a new sheet for the summary
    Set summarySheet = Worksheets.Add(After:=rawDataSheet)
    summarySheet.Name = "Hourly Summary"
    
    ' Add column headers for each hour and "Date"
    summarySheet.Cells(1, 1).Value = "Date"
    For i = 0 To 23
        summarySheet.Cells(1, i + 2).Value = Format(i, "00") & ":00"
    Next i
    
    ' Find the last row with data in the raw data sheet
    lastRow = rawDataSheet.Cells(rawDataSheet.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through raw data rows starting from the second row
    For i = 2 To lastRow
        ' Extract date and hour from "@timestamp"
        dateVal = DateValue(Left(rawDataSheet.Cells(i, 1).Value, InStr(1, rawDataSheet.Cells(i, 1).Value, ",") - 1))
        hourVal = Hour(TimeValue(Mid(rawDataSheet.Cells(i, 1).Value, InStr(1, rawDataSheet.Cells(i, 1).Value, "@") + 2)))
        
        ' Ensure the date exists in the summary sheet, add if not
        If Application.CountIf(summarySheet.Columns(1), dateVal) = 0 Then
            summarySheet.Cells(summarySheet.Rows.Count, 1).End(xlUp).Offset(1).Value = dateVal
        End If
        
        ' Find the row for the current date in the summary sheet
        summaryRow = Application.Match(dateVal, summarySheet.Columns(1), 0)
        
        ' Increment the counter for the specific hour
        If Not IsError(summaryRow) Then
            summarySheet.Cells(summaryRow, hourVal + 1).Value = summarySheet.Cells(summaryRow, hourVal + 1).Value + 1
        End If
    Next i
    
    ' Format the summary sheet
    With summarySheet
        .Rows(1).Font.Bold = True
        .Columns.AutoFit
    End With
    
    summarySheet.Activate
    

    End Sub

    erreur ici : hourVal = Hour(TimeValue(Mid(rawDataSheet.Cells(i, 1).Value, InStr(1, rawDataSheet.Cells(i, 1).Value, "@") + 2)))

  2. Assistant

    The error you're encountering with this line in your VBA code:

    hourVal = Hour(TimeValue(Mid(rawDataSheet.Cells(i, 1).Value, InStr(1, rawDataSheet.Cells(i, 1).Value, "@") + 2)))
    

    is likely due to the way you're using the Mid, InStr, and TimeValue functions to extract the hour from a timestamp in a specific format. Assuming your timestamp is in a format that includes an "@" character followed by the time in a recognizable time format (e.g., "@ 15:45:30"), the error could stem from several issues:

    1. Incorrect Timestamp Format: If the timestamp format in your raw data doesn't match the expected format (e.g., "Date, @ HH:MM" or another format leading up to "@" followed by the time), the Mid and InStr functions may not correctly extract the time portion. Ensure that every timestamp in your data includes an "@" character followed by the time in a consistent format.

    2. Invalid Time Value: If the extraction results in an invalid time string (not recognized by the TimeValue function), it will cause an error. This could happen if there are additional characters or unexpected formatting in the time string.

    3. Locale and Regional Settings: Sometimes, TimeValue can behave unexpectedly depending on the system's locale and regional time format settings. Ensure the extracted time string is in a format recognizable by your system's settings.

    Troubleshooting Steps:

    • Check the Data Format: Ensure that all timestamps in the raw data are in the expected format with the time portion following an "@" character.

    • Debug Print: Add a Debug.Print statement before the problematic line to print the value you're attempting to convert to a time. This can help verify that the extracted string is in a correct and expected format.

      Debug.Print Mid(rawDataSheet.Cells(i, 1).Value, InStr(1, rawDataSheet.Cells(i, 1).Value, "@") + 2)
      
    • Manual Validation: Test the Mid and InStr functions with a hardcoded value from your data to ensure they extract the time portion correctly.

    • Error Handling: Implement error handling to catch and skip rows with unexpected or incorrect formats. This can help isolate problematic entries.

      On Error Resume Next ' To skip over rows that cause errors
      hourVal = Hour(TimeValue(Mid(rawDataSheet.Cells(i, 1).Value, InStr(1, rawDataSheet.Cells(i, 1).Value, "@") + 2)))
      On Error GoTo 0 ' To turn back on normal error handling
      

    Adjustments to the code might be necessary depending on the exact nature of your data and the specifics of the error. Ensure that the timestamps are consistent and that the portion of the code responsible for extracting the time is correctly tailored to the format present in your data.