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]

My expection is only one text box to search a number within a range like 10 digit number mentioned below

2100050000 to 2100050099

for example if i want to find a number 2100050020 in worksheets,it goes to that cells containing searched number winth in a range like mentioned above start and end range it should go to select 2100050000 to 2100050099

 

[jameslou]

Aya, please post your sheet, what your'e trying to do is possible.
Let me see your template so that I will work on it.

 

[jindon]

1) Create a UserForm (UserForm1)
2) Add TextBox x 1 (TextBox1), CommandButton x 1 (CommandButton1) & ListBox x 1 (ListBox1)
To a standard module

Sub test()
    UserForm1.Show False
End Sub

to a UserForm module

Private Sub CommandButton1_Click()
Dim x As Long, ws As Worksheet, a, i As Long, ii As Long, b(), n As Long
With Me.TextBox1
    If (.Value = "") + (Not IsNumeric(.Value)) Then Exit Sub
    x = Application.RoundDown(.Value, -2)
End With
Me.ListBox1.Clear
For Each ws In Worksheets
    a = ws.UsedRange.Value
    For i = 1 To UBound(a,1)
        For ii = 1 To UBound(a,2)
            If (a(i,ii) >= x) * (a(i,ii) <= x + 99) Then
                n = n + 1
                ReDim Preserve b(1 To 3, 1 To n)
                b(1,n) = ws.Name : b(2,n) = Cells(i,ii).Address(0,0) : b(3,n) = a(i,ii)
            End If
        Next
    Next
Next
If n = 0 Then Exit Sub
With Me.ListBox1
    .Column = b
    .ColumnCount = 3
End With
End Sub
 
Private Sub ListBox1_Click()
With Me.ListBox1
    If .ListIndex = -1 Then Exit Sub
    Application.GoTo Sheets(.List(.ListIndex, 0)).Range(.List(.ListIndex,1)), True
End With
End Sub

 

[mindfullsilence]

I know what he wants. I need just about the same thing. He wants to find a number that is not on his spreadsheet. He wants excel to highlight 2 cells that lie below and above the number he's looking for. If he has 50 in one cell and 100 in the next cell, and he needs to find 75, he'd like excel to highlight 50 and 100.

 

[ndendrinos]

Mindfullsilence's interpretation of the question is right.

http://www.mrexcel.com/forum/showthread.php?t=106907

On a similar question Aladin gave me the above correct answer.
It will be interesting to see how this can be done with a macro
using a form with just one text box and a command button.

 

[ayazgreat]

Sir

i found error in this line

For i = 1 To UBound(a,1)

 

[jindon]

My code doesn't suite your requirement since your explanation was not clear to me.
1) What is the sheet name that you have the data
2) What column you need to search

 

[ndendrinos]

This is an interseting question and a pity to let it go unanswered
Maybe Jindon will continue this thread assuming that the sheet name is Sheet1 and the data is in column A & B
example :
A1 = 1
B1 = 5
A2 = 6
B2 = 10
Searching for number 4 the expectation is that A1 & B1 are highlighted.
Thank you

 

[jindon]

Sub test()
Dim myWhat, x
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("b:b"), 1)
    If Not IsError(x) Then
        .Select
        .Range("a" & x).Resize(,2).Select
    Else
        MsgBox "No such data"
    End If
End With
End Sub

 

[ndendrinos]

Hello Jindon ,
Typed 4 and got "no such data" (should have highlighted A1:B1)

Typed 7 and it highlights A1:B1 (should have highlighted A2:B2)