Filter and Loop Through A List Of Records For Min Date

Looking for help with some VBA.

I have a worksheet with a list of records that has a: Status cell, Date cell and Job cell.

When I change the Status cell to "Filled" in the active record I want to filter the list of records to only look at records in the list that have the same entry as in the Job cell of the active record, say the letter "a". If there are older dates than in the active record Date cell of the filtered list, I would want to enter the older date (Date-1) in the active Date cell.

Example

Status Date Job
1 Sent 3/10/2018 a
2 Sent 5/10/2018 b
3 Filled 4/10/2018 a
4 Rejected 5/15/2018 c
5 Interviewed 6/14/2018 b
6 Sent 5/15/2018 a


Record 3 would get Date changed to 3/9/2018 form 4/10/2018 when the Status cell is changed to "Filled"

1 Answer
1

A worksheet_change event sub procedure can handle the change in the status column's value and an array can replace filtering the data.

Put this in the worksheet's private code sheet (right-click worksheet name tab, View Code).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(1)) Is Nothing Then
On Error GoTo safe_exit
Application.EnableEvents = False
Dim i As Long, t As Range, tmp As Variant
For Each t In Intersect(Target, Columns(1))
If LCase(t.Value2) = "filled" Then
tmp = Intersect(Me.UsedRange, Range("B:C")).Value2
For i = LBound(tmp, 1) To UBound(tmp, 1)
If tmp(i, 2) = t.Offset(0, 2) Then _
t.Offset(0, 1) = Application.Min(t.Offset(0, 1), tmp(i, 1))
Next i
End If
Next t
End If

safe_exit:
Application.EnableEvents = True

End Sub


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.