VBA Update a sub set of records

[Al Del]

I am using the following macro to place a "w" in column M (9) of the target record of a worksheet when colmun D gets the entry "Filled".

    Dim lastrow As Integer
    lastrow = Range("D" & Rows.Count).End(xlUp).row
    
    'If Status is "Filled" place a "w" in Results
    'Range("D2:D" & lastrow) Is the Target it defines the column D as the target area
    If Not Intersect(Target, Range("D2:D" & lastrow)) Is Nothing Then
        If Target <> "Filled" Then
            Target.Offset(, 9) = vbNullString
            Else: Target.Offset(, 9) = "w"
        End If
     End If

I would like to enter a "w" into col M of all records, that have the same entry in column K of the target row of the list, when anyone of these records gets the entry "Filled" put in col D of one of these records.

 

[JLGWhiz]

that have the same entry in column K of the target row of the list

Same as what? w? Filled?

 

[Al Del]

When "Filled" is entered in the target record in col D, I want "w" to be entered in all records, col M, that have the same entry in col K.

 

[JLGWhiz]

When "Filled" is entered in the target record in col D, I want "w" to be entered in all records, col M, that have the same entry in col K.

It is still unclear as to what you want column K value to be the same as. Can you show a before and after example of what you expect?
Col D|Col K|Col M
Filled|---?--|--w--

 

[Al Del]

Column K would not change, it is used to define the subset of records to be updated with the "w" in col M.
Ideally I would like to enter the oldest date in the subset in the target record date cell, col H.
The numbers are just for reference.

Before column D change  
  Col D        Col H         Col K    Col M
  Status      Date          Job        Result
1 Sent        3/10/2018    a        
2 Sent        5/11/2018    b        
3 Interview  4/12/2018    a        
4 Rejected   5/15/2018    c        
5 Interview  6/14/2018    b        
6 Sent        7/07/2018    a        

After Enter Filled in col D, record 3.
3 Filled        3/10/2018    a       w
1 Sent        3/10/2018    a        w
6 Sent        7/07/2018    a        w
2 Sent        5/11/2018    b        
4 Rejected   5/15/2018    c        
5 Interview  6/14/2018    b

 

[Macropod]

Cross-posted at: https://www.ozgrid.com/forum/forum/.../1206159-update-a-sub-set-of-records-with-vba & https://stackoverflow.com/questions/51574889/vba-update-a-sub-set-of-records
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[JLGWhiz]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        If LCase(Target.Value) = "filled" Then
            Range("K:K").AutoFilter 1, Target.Offset(, 7).Value
            Range("K2", Cells(Rows.Count, "K").End(xlUp)).Offset(, 2).SpecialCells(xlCellTypeVisible).Value = "w"
            AutoFilterMode = False
        End If
    End If
Application.EnableEvents = True
End Sub

In the futue, you should notify us if you are cross posting so we can check to see if a solution has been provided in another forum and not spend a lot of time duplicating effort.

 

[Al Del]

Yes I will, thank you.

I ran the procedure and got an AutoFillter out of range error, turns out the header of my table starts at row 2, so I think I made the correct changes. If I input "Filled" col D no update of column M occurs. I'm using a table not a range in case that makes a difference.

 

[JLGWhiz]

Yes I will, thank you.

I ran the procedure and got an AutoFillter out of range error, turns out the header of my table starts at row 2, so I think I made the correct changes. If I input "Filled" col D no update of column M occurs. I'm using a table not a range in case that makes a difference.

I am going to drop off of this thread. When you can get all of your parameters defined so that they are clear to the reader and can properly describe your worksheet layout and conditions, maybe someone else will pick up on the thread and provide assistance.
Regards, JLG

 

[Al Del]

Ok, I think the only difference was the starting row. I actually moved the table header to the first row and still no update result?