A la recherche de ... la bonne valeur!

Public Function Cherchedansunecolonne(ByVal Mycol As Range, Ref As String) As Boolean

    Dim c As Range
    
    With Range(Mycol.Address)
        Set c = .Cells.Find(Ref, , xlValues, xlWhole)
    End With
    
    If Not c Is Nothing Then
    Cherchedansunecolonne = True
    Else
    Cherchedansunecolonne = False
    End If
    
End Function

Public Function rechercheIP(ByRef Ip1 As Range, Ip2 As Range, Nomdomaine As Range, Ref_ip As Double) As Variant

    Dim i, h As Long
    Dim Plage1, Plage2, Plage3, c As Range
    Dim Nomfeuil1 As String
    
    Set Plage1 = Ip1
    Set Plage2 = Ip2
    Set Plage3 = Nomdomaine
    
    Dim tabl1(), tabl2(), tabl3()
    
    If Plage1.Count = Plage2.Count And Plage2.Count = Plage3.Count Then
    
    If Plage1.Columns.Count = 1 And Plage2.Columns.Count = 1 And Plage2.Columns.Count = 1 Then
    
    tabl1() = Plage1.Value
    tabl2() = Plage2.Value
    tabl3() = Plage3.Value
    
        For i = LBound(tabl1()) To UBound(tabl1())
    
            If Ref_ip <= tabl1(i, 1) And Ref_ip >= tabl2(i, 1) Then
            rechercheIP = tabl3(i, 1)
            Exit Function
            End If
        
            If Ref_ip >= tabl1(i, 1) And Ref_ip <= tabl2(i, 1) Then
            rechercheIP = tabl3(i, 1)
            Exit Function
            Else
            rechercheIP = "Hors-limite"
            Exit Function
            End If
        
        Next i
        
    Else
   
    End If
    End If
        
    rechercheIP = "Les plages de données ne sont pas de même longueur!"
        
    
End Function