Snasui Excel Forum

Code: Select all

Sub CheckIPInterVal()
    Dim r As Range, rAll As Range
    Dim rt As Range, rtAll As Range
    Dim lng As Long, ts As Variant
    Dim tt As Variant, c As Boolean
    With Sheets("Sheet2")
        Set rAll = Sheet2.Range("e3", _
            .Range("e" & Rows.Count).End(xlUp))
        Set rtAll = Sheet1.Range("c17", _
            .Range("c" & Rows.Count).End(xlUp))
    End With
    lng = 2
    For Each r In rAll
        c = False
        For Each rt In rtAll
            ts = Split(r, ".")
            tt = Split(rt, ".")
            If CInt(ts(0)) >= CInt(tt(0)) And _
                CInt(ts(1)) >= CInt(tt(1)) And _
                CInt(ts(2)) >= CInt(tt(2)) And _
                CInt(ts(3)) >= CInt(tt(3)) Then
                    tt = Split(rt.Offset(0, 1), ".")
                    If CInt(ts(0)) <= CInt(tt(0)) And _
                        CInt(ts(1)) <= CInt(tt(1)) And _
                        CInt(ts(2)) <= CInt(tt(2)) And _
                        CInt(ts(3)) <= CInt(tt(3)) Then
                        lng = lng + 1
                        r.Offset(0, 1) = "Y"
                        c = True
                        Exit For
                    End If
            End If
        Next rt
        If c = False Then
            r.Offset(0, 1) = "N"
        End If
    Next r
End Sub

Code: Select all

'...Other code
For Each r In rAll
        c = False
        For Each rt In rtAll
            If r = "N/A" Then
                r.Offset(0, 1) = "N/A"
                c = True
                Exit For
            End If
            ts = Split(r, ".")
            tt = Split(rt, ".")
'...Other code