Excel VBA to fill empty cells that satisfy condition-CodePudding

I am trying to find an excel VBA solution to the following problem,

I have a dataset in which some cells are empty and they need to be filled based on a condition. The condition is that it would be filled with station number corresponding to the one with a date range of /- 5 days. Also, the lot number should match for these rows.

Please help!

For example please see the example in the image.

CodePudding user response：

This should do the trick, I just did it so try it and let us know if it work. And don't forget to change the name with the one of your sheet in the third line.

Sub FillBlank()
    Dim F As Worksheet
    Set F = ThisWorkbook.Worksheets("Feuil1")
    Dim i As Long: i = 0
    Dim j As Long
    Do While F.Range("D2").Offset(i, 0) <> ""
        If F.Range("A2").Offset(i, 0) = "" Then
            j = 0
            Do While F.Range("D2").Offset(j, 0) <> ""
                If (Abs(DateDiff("d", F.Range("D2").Offset(i, 0).Value, F.Range("D2").Offset(j, 0).Value)) <= 5) And (F.Range("C2").Offset(i, 0) = F.Range("C2").Offset(j, 0)) Then
                    F.Range("A2").Offset(i, 0) = F.Range("A2").Offset(j, 0).Value
                    GoTo Next_Blank
                End If
            j = j   1
            Loop
        End If
Next_Blank:
    i = i   1
    Loop
End Sub

CodePudding user response：

It worked like a charm. Thanks a ton for this! Really appreciate it.