Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

In my excel worksheet, Column A2:A400 contains date values for events. The row values in…

  1. User

    In my excel worksheet, Column A2:A400 contains date values for events. The row values in column A repeat the date down column A as groups of the same date. When a new date starts in column A it will also repeat down column A as a group of the same date The earliest group of dates start at the top of the column. Column E has time values for the closing time of each activity on the Row. Some the row values in column E also repeat the same time.

    The VBA code below is supposed to do the following;

    Look down column A and identify each group of dates that match. It should then find the latest time value in column E for each unique group of dates and append the text 'Close' to the same row in column V for each group of dates where the latest time is found.

    Unfortunately the code below is not doing this.

    Sub AppendCloseToColumnV() Dim dict As Object Set dict = CreateObject("Scripting.Dictionary")

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    Dim currentDate As Date
    Dim currentMaxTime As Date
    
    For i = 2 To lastRow
        Dim dateValue As Date
        Dim timeValue As Date
        
        If IsDate(Cells(i, "A").Value) Then
            dateValue = Cells(i, "A").Value
        Else
            GoTo NextIteration
        End If
        
        If IsDate(Cells(i, "E").Value) Then
            timeValue = Cells(i, "E").Value
        Else
            GoTo NextIteration
        End If
        
        If dict.Exists(dateValue) Then
            If dict(dateValue) < timeValue Then
                dict(dateValue) = timeValue
            End If
        Else
            dict.Add dateValue, timeValue
        End If
        
    

    NextIteration: Next i

    For i = 2 To lastRow
        If IsDate(Cells(i, "A").Value) Then
            currentDate = Cells(i, "A").Value
            currentMaxTime = dict(currentDate)
        Else
            GoTo NextIteration2
        End If
        
        If Cells(i, "E").Value = currentMaxTime Then
            Cells(i, "V").Value = Cells(i, "V").Value & "Close"
        End If
        
    

    NextIteration2: Next i

    Dim groupStartDate As Date
    Dim groupEndDate As Date
    Dim appendClose As Boolean
    
    ' Loop through the rows to process the groups of dates
    For i = 2 To lastRow
        If IsDate(Cells(i, "A").Value) Then
            ' Check if the current date is the start of a new group
            If i = 2 Or Cells(i, "A").Value <> groupStartDate Then
                groupStartDate = Cells(i, "A").Value
                groupEndDate = dict(groupStartDate)
                appendClose = False
            End If
            
            ' Check if the current row is within the group
            If groupEndDate = Cells(i, "E").Value Then
                appendClose = True
            End If
            
            ' Append "Close" if within the group
            If appendClose Then
                Cells(i, "V").Value = Cells(i, "V").Value & "Close"
            End If
        End If
    Next i
    

    End Sub

  2. Assistant

    The issue with the code is that it does not properly identify and process the groups of dates. Here is an updated version of the code that should achieve the desired result:

    Sub AppendCloseToColumnV()
        Dim dict As Object
        Set dict = CreateObject("Scripting.Dictionary")
        
        Dim lastRow As Long
        lastRow = Cells(Rows.Count, "A").End(xlUp).Row
        
        Dim i As Long
        Dim currentDate As Date
        Dim currentMaxTime As Date
        
        For i = 2 To lastRow
            Dim dateValue As Date
            Dim timeValue As Date
            
            If IsDate(Cells(i, "A").Value) Then
                dateValue = Cells(i, "A").Value
            Else
                GoTo NextIteration
            End If
            
            If IsDate(Cells(i, "E").Value) Then
                timeValue = Cells(i, "E").Value
            Else
                GoTo NextIteration
            End If
            
            If dict.Exists(dateValue) Then
                If dict(dateValue) < timeValue Then
                    dict(dateValue) = timeValue
                End If
            Else
                dict.Add dateValue, timeValue
            End If
            
    NextIteration:
        Next i
        
        Dim groupEndDate As Date
        Dim appendClose As Boolean
        
        ' Loop through the rows to process the groups of dates
        For i = 2 To lastRow
            If IsDate(Cells(i, "A").Value) Then
                ' Check if the current date is the start of a new group
                If i = 2 Or Cells(i, "A").Value <> currentDate Then
                    currentDate = Cells(i, "A").Value
                    currentMaxTime = dict(currentDate)
                    groupEndDate = currentMaxTime
                    appendClose = False
                End If
                
                ' Check if the current row is within the group
                If Cells(i, "E").Value = currentMaxTime Then
                    appendClose = True
                End If
                
                ' Append "Close" if within the group
                If appendClose Then
                    Cells(i, "V").Value = Cells(i, "V").Value & "Close"
                End If
                
                ' Check if the current row is the end of the group
                If Cells(i, "E").Value < groupEndDate Then
                    groupEndDate = Cells(i, "E").Value
                ElseIf Cells(i, "A").Value <> currentDate Then
                    groupEndDate = currentMaxTime
                End If
            End If
        Next i
        
    End Sub
    

    This code should correctly identify the start and end of each group of dates and determine whether to append "Close" to the corresponding rows in column V.

  3. User

    It is still not recognising the group of rows that contain the same dates

  4. Assistant

    Apologies for the confusion. I misunderstood the requirement initially. Here is an updated version of the code that correctly identifies the groups of rows with the same dates and appends "Close" to the rows where the latest time is found:

    Sub AppendCloseToColumnV()
        Dim dict As Object
        Set dict = CreateObject("Scripting.Dictionary")
        
        Dim lastRow As Long
        lastRow = Cells(Rows.Count, "A").End(xlUp).Row
        
        Dim i As Long
        Dim currentDate As Date
        Dim currentMaxTime As Date
        
        For i = 2 To lastRow
            Dim dateValue As Date
            Dim timeValue As Date
            
            If IsDate(Cells(i, "A").Value) Then
                dateValue = Cells(i, "A").Value
            Else
                GoTo NextIteration
            End If
            
            If IsDate(Cells(i, "E").Value) Then
                timeValue = Cells(i, "E").Value
            Else
                GoTo NextIteration
            End If
            
            If dict.Exists(dateValue) Then
                If dict(dateValue) < timeValue Then
                    dict(dateValue) = timeValue
                End If
            Else
                dict.Add dateValue, timeValue
            End If
            
    NextIteration:
        Next i
        
        Dim groupStartDate As Date
        Dim groupEndDate As Date
        Dim appendClose As Boolean
        
        ' Loop through the rows to process the groups of dates
        For i = 2 To lastRow
            If IsDate(Cells(i, "A").Value) Then
                ' Check if the current date is the start of a new group
                If i = 2 Or Cells(i, "A").Value <> groupStartDate Then
                    groupStartDate = Cells(i, "A").Value
                    groupEndDate = dict(groupStartDate)
                    appendClose = False
                End If
                
                ' Check if the current row is within the group
                If Cells(i, "E").Value = groupEndDate Then
                    appendClose = True
                End If
                
                ' Append "Close" if within the group
                If appendClose Then
                    Cells(i, "V").Value = Cells(i, "V").Value & "Close"
                End If
            End If
        Next i
    End Sub
    

    This updated code should correctly identify the groups of rows with the same dates and append "Close" to the rows where the latest time is found. Let me know if this resolves the issue.

  5. User

    The code might be recognising the group of dates, but it is not appending the value 'Close' to the same row where the latest time for that group was found

  6. Assistant

    Apologies for the confusion. Here is an updated version of the code that correctly appends "Close" to the rows where the latest time for each group of dates is found:

    Sub AppendCloseToColumnV()
        Dim dict As Object
        Set dict = CreateObject("Scripting.Dictionary")
        
        Dim lastRow As Long
        lastRow = Cells(Rows.Count, "A").End(xlUp).Row
        
        Dim i As Long
        Dim currentDate As Date
        Dim currentMaxTime As Date
        
        For i = 2 To lastRow
            Dim dateValue As Date
            Dim timeValue As Date
            
            If IsDate(Cells(i, "A").Value) Then
                dateValue = Cells(i, "A").Value
            Else
                GoTo NextIteration
            End If
            
            If IsDate(Cells(i, "E").Value) Then
                timeValue = Cells(i, "E").Value
            Else
                GoTo NextIteration
            End If
            
            If dict.Exists(dateValue) Then
                If dict(dateValue) < timeValue Then
                    dict(dateValue) = timeValue
                End If
            Else
                dict.Add dateValue, timeValue
            End If
            
    NextIteration:
        Next i
        
        ' Loop through the rows to append "Close" to the rows where the latest time is found
        For i = 2 To lastRow
            If IsDate(Cells(i, "A").Value) Then
                currentDate = Cells(i, "A").Value
                currentMaxTime = dict(currentDate)
                
                If Cells(i, "E").Value = currentMaxTime Then
                    Cells(i, "V").Value = Cells(i, "V").Value & "Close"
                End If
            End If
        Next i
    End Sub
    

    This updated code should correctly identify the groups of rows with the same dates and append "Close" to the rows where the latest time for each group is found. Let me know if this resolves the issue.