Match Textbox Value in Range (Between values mentioned in two Cells)

[waheed254]

Hi There, I want to check text box value if matches between range defined accordingly..

Example

I have detail already given as under,

COLUMN A	COLUMN B	COLUMN C
ABC	101	200
DEF	301	400

Now i want to check Text box1 value which is ABC in Columns A
& check if text box2 value is within range of Column B to Column C Value if yes than ok if not Error Msg.

Can any one Guide
ABC can be more than once so all matches should be checked.

 

[dmt32]

Hi,
Try following code I adapted from VBA help file (Range.FindNext Method)

Private Sub CommandButton1_Click()
    Dim SearchValue(1 To 2) As Variant
    Dim firstaddress As String
    Dim FoundMatch As Boolean
    Dim c As Range
   
    SearchValue(1) = Me.TextBox1.Value
    SearchValue(2) = Val(Me.TextBox2.Value)

    With Worksheets(1).Columns(1)
         Set c = .Find(SearchValue(1), LookIn:=xlFormulas, lookat:=xlWhole)
         If Not c Is Nothing Then
            firstaddress = c.Address
            Do
               FoundMatch = CBool(SearchValue(2) >= c.Offset(, 1).Value And _
                                  SearchValue(2) <= c.Offset(, 2).Value)
                                 
               If FoundMatch Then GoTo DoneFinding
                Set c = .FindNext(c)
                If c Is Nothing Then GoTo DoneFinding
            Loop While c.Address <> firstaddress
          End If
    End With

DoneFinding:
    If FoundMatch Then
        MsgBox "Record Found"
    Else
        MsgBox "Record Not Found"
    End If

End Sub

Adjust code as required to meet specific project need

Dave

 

[waheed254]

After a bit adjustment as per project requirement, Worked Fine. Thnx.

 

[dmt32]

After a bit adjustment as per project requirement, Worked Fine. Thnx.

Welcome glad suggestion helped.
Many thanks for feedback

Dave