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

 

[ayazgreat]

Sir Still wait for your reply

 

[jindon]

Sir
You have done great job I appreciate your efforts and thank you but a question hits upon me that you have defined in these codes a specified sheet1 and specified columns a and b if I say number to be searched in worksheets in all cells so could it be possible?

I don't understand what you mean.
Show us an example.

 

[ayazgreat]

Sir
your codes finds to select a number in sheet1 in columns a and b if i want to search a number within range in worksheets in any row or column to select. Alt last I want to find a numuber in all sheets to select.

 

[jindon]

Sub test()
Dim myWhat, x, y, myRow As Long
myWhat = Application.InputBox("What to search?", type:=1)
If myWhat = False Then Exit Sub
With Selection.Resize(,2)
    x = Application.Match(myWhat, .Columns(1), 0)
    y = Application.Match(myWhat, .Columns(2), 0)
    If Not IsError(x) Then
        myRow = x
    ElseIf Not IsError(y) Then
        myRow = y
    End If
    If myRow = 0 Then
        x = Application.Match(myWhat, .Columns(1), 0)
        y = Application.Match(myWhat, .Columns(2), 0)
        If Not IsError(x) Then
            If Not IsError(y) Then
                If x <> y Then
                    myRow = x
                Else
                    MsgBox "No such data"
                End If
            Else
                myRow = x
            End If
         Else
             MsgBox "No such data"
         End If
    End If
    If myRow > 0 Then .Rows(myRow).Select
End With
End Sub

 

[ayazgreat]

Sir
Thanks for your reply but your given codes do not work to find a number to select in worksheets and give message not found.

 

[jindon]

Sir
Thanks for your reply but your given codes do not work to find a number to select in worksheets and give message not found.

Sorry, but I can not think of any other situation from your "Explanations".
I'll just go away.

 

[ayazgreat]

Sir
Simply I mean to say to look for all worksheet's cells for a searched number as your previouse codes only look for only sheet1 and columns a and b for a searhed number

 

[ayazgreat]

Sir 
Simply I mean to say to look for all worksheet's cells for a searched number as your previouse codes only look for only sheet1 and columns a and b for a searhed number

Could anyone help

 

[ayazgreat]

I want to ask something more, if mentioned below codes can be change to Sheet even codes instead of using input box, codes search mywhat in same x and y.

Like when I enter numbers in below range and if number found within a range then both cell highlighted as mentoned below example.

Excel Workbook
	A	B
1	Start Range	End Range
2	21006	21007
3	21010	21015
4	21406	21412
5	81000	81006
6	81023	81026
7	80300	80301
8	80804	80812
9	21011	21011
10	80304	80311
11	81025	81026
Temp

Sub test()
Dim myWhat, x, y, myRow As Long
myWhat = Application.InputBox("What to search?", Type:=1)
If myWhat = False Then Exit Sub
With Sheets("sheet1")
    .Select
    x = Application.Match(myWhat, .Range("a:a"), 0)
    y = Application.Match(myWhat, .Range("b:b"), 0)
    If Not IsError(x) Then
        myRow = x
    ElseIf Not IsError(y) Then
        myRow = y
    End If
    If myRow = 0 Then
        x = Application.Match(myWhat, .Range("a:a"), 1)
        y = Application.Match(myWhat, .Range("b:b"), 1)
        If Not IsError(x) Then
            If Not IsError(y) Then
                If x <> y Then
                    myRow = x
                Else
                    MsgBox "No such data"
                End If
            Else
                myRow = x
            End If
         Else
             MsgBox "No such data"
         End If
    End If
    If myRow > 0 Then .Range("a" & myRow).Resize(, 2).Interior.Color = 37
End With
End Sub

Thanks in advance

 

[ayazgreat]

Could anybody answer it please