Find a number within a range

[ayazgreat]

Sir
I want to ask subject question , all I wanted to do is ask that can it be possible, we can find a number within a range through user form.

In a workbook ,having 4 or 5 sheets , diffrent ranges are scattered with their start range and end range like mentined below
Start Range End Range
2100100 2100199
2130100 2130199

If I want to search number like 2100125, then a user form finds it and goes to that cell containing searched number

 

[jindon]

Correction

Can you just change to

x = Application.Match(myWhat, .Range("a:a"), 1)

 

[ndendrinos]

Excellent as always.
Thank you Jindon

 

[ndendrinos]

Small problem ...
I type 11 and A2: B2 is highlighted (should be no such data}

 

[jindon]

Ok then
try

Sub test()
Dim myWhat, x, y
myWhat = InputBox("what to search?")
If (myWhat = False) + (myWhat = "") Then Exit Sub
If IsNumeric(myWhat) Then myWhat = CDbl(myWhat)
With Sheets("sheet1")
    x = Application.Match(myWhat, .Range("a:a"), 1)
    y = Application.Match(myWhat, .Range("b:b"), 1)
    If Not IsError(x) Then
        If x <> y Then
            .Select
            .Range("a" & x).Resize(,2).Select
        Else
            MsgBox "No such data"
        End If
    Else
        MsgBox "No such data"
    End If
End With
End Sub

 

[ndendrinos]

Now works great for all rows EXCEPT the first row.
I type 3 get Type mismatch

 

[jindon]

Can you change

If x <> y Then

to

If (IsError(y)) + (x <> y) Then

 

[ndendrinos]

Not yet with this last correction BUT maybe this will help:
I type 1 I get "type mismatch , same thing if I type 2 or 3 or 4 BUT if I type 5 I get No such data

 

[jindon]

Hummm
try
change

    If Not IsError(x) Then
        If x <> y Then
            .Select
            .Range("a" & x).Resize(,2).Select
        Else
            MsgBox "No such data"
        End If
    Else
        MsgBox "No such data"
    End If

to

    If Not IsError(x) Then
        If Not IsError(y) Then
            If x <> y Then
                .Select
                .Range("a" & x).Resize(,2).Select
            Else
                MsgBox "No such data"
            End If
         Else
            .Select
            .Range("a" & x).Resize(,2).Select
         End If
    Else
        MsgBox "No such data"
    End If

 

[ndendrinos]

with this last correction it woks on all EXCEPT the number shown in column B (here number 5 and 10) get no such data ...

 

[ndendrinos]

I am truly sorry ... got confused. Pls disregard my last observation ... All works well WITH THANKS JINDON.
Regards, Nick
CORRECTION: PROBLEM AS NOTED IN LAST POST IS STILL ON ... does not work when I type 5 or 10 (the excat value in column B ... added more rows with same problem