Thread: Find the latest Date from one column across particular rows

Try this:

Sub LastActual()
Dim LR As Long, i As Long, Val As String
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LR
        If Cells(i, "B") = "Sentence Process" Then _
            If Cells(i, "D") > Val Then Val = Cells(i, "D")
    Next i

Range("G1") = Val
MsgBox ("Updated in G1")
End Sub

Just on the off chance you're interested you can do the same without VBA:

G1: =MAX(IF($B$1$:$B$100="Sentence Process",$D$1:$D$100))
confirmed with CTRL + SHIFT + ENTER

Even if you opt for VBA pending volume of data you may find running an Evaluation of the above to be quicker than iterating each row in the data set.

LR just means "Last Row", so you shouldn't need to change that.

"i" is just a progressive variable. Once you've gone through the FOR/NEXT loop completely for the "Sentence Process", you can just use the loop AS IS for the next value just changing the search to "Module Strip Process".

The only things you need to edit are the search words and where you write it down when you're done.

Sub LastActual()
Dim LR As Long, i As Long, Val As String
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LR
        If Cells(i, "B") = "Sentence Process" Then _
            If Cells(i, "D") > Val Then Val = Cells(i, "D")
    Next i
    Range("G1") = Val

    Val = 0
    For i = 2 To LR
        If Cells(i, "B") = "Module Strip Process" Then _
            If Cells(i, "D") > Val Then Val = Cells(i, "D")
    Next i
    Range("G2") = Val

MsgBox ("Done")
End Sub

That's just to show you what you were trying to do. However that means you have loop through the range fully TWICE, and THREE times if you add another level. That's not efficient at all.

If you're going to keep expanding, I'd expand my variables as well and store the answer for each one as we loop through the code only once, then write out all the variable at once.

Sub LastActuals()
Dim LR As Long, i As Long, test As String
v1 As String, v2 As String, v3 As String
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LR
        test = Cells(i, "B").Value
        Select Case test
            Case "Sentence Process"
                If test > v1 Then v1 = test
            Case "Module Strip Process"
                If test > v2 Then v2 = test
            Case "Engine Strip Process"
                If test > v3 Then v3 = test
        End Select
    Next i
    
Range("G1") = v1
Range("G2") = v2
Range("G3") = v3
End Sub