In my excel worksheet, Column A2:A400 contains date values for events. The row values in…
- 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 IfNextIteration: 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 IfNextIteration2: 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 iEnd Sub
- 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 SubThis 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.
- User
It is still not recognising the group of rows that contain the same dates
- 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 SubThis 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.
- 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
- 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 SubThis 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.