Challenge is to identify all the "overlaps" between the policies in Col E taken out against the same loan in Col B.
Attached Macro works perfectly where there is an overlap between consecutive rows, but I can't work out how to make it "see" the overlaps that may be on a non-consecutive row, and map that overlap.
E.g. Rows 10 - 12 show loan 193672 has been covered by Policies 9, 10 and 11. The Macro identifies the overlap between Policies 9 and 10, and between 10 and 11, but "misses" that there is still an overlap between Policies 9 and 11 after Policy 10 expires.
Option Explicit
Sub OVERLAPS()
Dim f As Long, n As Long
f = Cells(Rows.Count, 2).End(xlUp).Row
For n = 3 To f
'Check for overlaps
If Range("B" & n) = Range("B" & n - 1) Then
If Range("F" & n) < Range("G" & n - 1) Then
If Range("G" & n) < Range("G" & n - 1) Then
Range("H" & n) = Range("F" & n) & "- " & Range("G" & n)
Else: Range("H" & n) = Range("F" & n) & "- " & Range("G" & n - 1)
End If
End If
End If
Next
End Sub
Cols M - AB demonstrate the overlaps that should be captured.
Hope it's clear, and all suggestions, pointers and solutions welcome as ever.
Ochimus
Bookmarks