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

 

[Marinus]

I am trying to do something similar. My column "A" contains a long list of random (whole) numbers. I like to type one of these numbers in a box near the top of the worksheet, either in a certain cell or in a message box. When I click a button, running a macro, I would like this to activate (go to) the cell in column A, containing this number. Any help would be appreciated. Find and GoTo functions I am aware of don't fully accommodate this.

 

[jindon]

Marinus

I think you'd better make a new thread of your own.
So that we can tailor the code for your need.

 

[ayazgreat]

I think it can be resolved but do not how and who will give answer?

 

[Marinus]

Sorry I added a question. I am new to this, and I'll start my own thread in a few hours. To get back to the original question: perhaps you can filter each page for the number, then use SHIFT and arrow keys to activate the number, and then undo the filter. Good luck.

 

[Marinus]

I think I found an answer. In this example you should start by creating random number lists in sheet 1 and 2 of a new workbook, between cells A10 and A500. Then type one of these numbers in cell F4 of sheet one.
Next run the following macro to select the number in one of the lists:

Sub Macro1()
Range("F2").Select
ActiveCell.FormulaR1C1 = "Number"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=R[7]C[-5]=R4C6"
Range("A9:A500").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("F2:F3"), Unique:=False
Range("A1").Select
Selection.End(xlDown).Select
'Selection.End(xlDown).Select (optional, if you have a list header to get past)
ActiveSheet.ShowAllData
If ActiveCell = 0 Then GoTo Line1 Else GoTo Line2
Line1:
Sheets("Sheet2").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=R[7]C[-5]=R4C6"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Range("A9:A500").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("F2:F3"), Unique:=False
Range("A1").Select
Selection.End(xlDown).Select
'Selection.End(xlDown).Select (optional, if you have a list header to get past)
ActiveSheet.ShowAllData
Line2:
End Sub

You can add to this macro to search in multiple sheets, and you can hide the filter criteria (in this case row 2 and 3). This may be crude, but it seems to work.

 

[VoG]

ayazgreat

Does jindon's code work if you add a second textbox to your form? If so, the code could be modified to extract the numbers from a single textbox but please check if it works with two textboxes first.

 

[ayazgreat]

Sir

I have already checked it out but it could not work as i wanted and it did not go to these cells containing the searched number.

 

[jindon]

Sir

I have already checked it out but it could not work as i wanted and it did not go to these cells containing the searched number.

Show me your code.

 

[ayazgreat]

Jindon, it is your codes which you had given me please mentioned below

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]

And how is it not working as you expected?