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]

I have asked this question but nobody gave me reply but at least could you please tell me that could it be possible?

 

[iliace]

So it other words, if you have a form like this one and you put in the values that I put in it:

You want to look for the first number that is between those two values?

 

[ayazgreat]

I want to look for any number between any range in my workbook and it goes to that cell cantaining my searched number.

 

[iliace]

I have asked this question but nobody gave me reply but at least could you please tell me that could it be possible?

Yes, it's possible.

 

[ayazgreat]

If this possible can you please tell me how can I do it through a user form?

 

[jindon]

Private Sub CommandButton1_Click()
Dim a, i As Long, ii As Integer, ws As Worksheet, flg As Boolean
Dim num1 As Double, num2 As Double
num1 = CDbl(TextBox1.Value)
num2 = CDbl(TextBox2.Value)
For Each ws In Sheets
    a = ws.Range("a1", ws.UsedRange.SpecialCells(11))
    If Not IsArray(a) Then
        For i = 1 To UBound(a,1)
            For ii = 1 To UBound(a,2)
                If (a(i,ii)>= num1) * (a(i,ii) <= num2) Then
                    flg = True
                    If vbYes = MsgBox("Found " & a(i,ii) & vbLf & "Stop?",vbYesNo) Then
                        Application.GoTo ws.Cells(i,ii), True
                        Exit Sub
                    End If
                End If
            Next
        Next
    End If
Next
If Not flg Then MsgBox "Not found"
End Sub

 

[jindon]

correction

If Not IsArray(a) Then

shoud be

If IsArray(a) Then

 

[ayazgreat]

Thanks Sir for your reply but let me explain you in detail that what i Want to ask

In user form i have only one text box where can be entered only one number to be searched in worksheets which would be between in start and end range like mentioned below example

Srat Range End Range
1200 1299

and I if would like to search 1214 which lies betwee said ranges, and go to these cells or select these cells containing searched number.

 

[Aussiebear]

Why use a form? Why do you not want to use Excel's Find Function Ctrl + F. After all it will search for a number or text value, find all the occurances of that which you are looking for, and go to that cell or cells.

From all that which you have indicated so far, I can only assume you are trying to re invent the wheel.

 

[ayazgreat]

Dear

Ctrl f excel function is not able to search a number which lies between the ranges,