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]

jdc,

Sorry, It is "Sheet event codes" when I write number in target columns ,

Acutaly Jindon code works well, it runs from module and I want it to be coverted into sheet venet code.

Temp

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 86px"><COL style="WIDTH: 104px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">Start Range</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">End Range</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">21006</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">21007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21010</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21015</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">21406</TD><TD style="TEXT-ALIGN: right">21412</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">81000</TD><TD style="TEXT-ALIGN: right">81006</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81023</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81026</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">80300</TD><TD style="TEXT-ALIGN: right">80301</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">80804</TD><TD style="TEXT-ALIGN: right">80812</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21011</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">80304</TD><TD style="TEXT-ALIGN: right">80311</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81025</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">81026</TD></TR></TBODY></TABLE>

 

[HarrisM]

Something like this, perhaps? In Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value <> "" Then
    myWhat = Range("E1").Value
    test (myWhat)
    End If
End Sub

Just a small change to the sub line of jindon's code would be needed...

Sub test(myWhat As Long)

That would fire jindon's script whenever a cell was changed which would be overkill, but should give you an idea. Otherwise, to fire the code when changing a specific cell, you could use:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.address ="$E$1" Then
    myWhat = target.value
    test (myWhat)
    End If
End Sub

 

[ayazgreat]

HarrisM
Why are you targeting only range E1 , as you coud see my below example lying in columns a and b , mywhat should be targeted when I enter number in column b after entering number in column a. Have you tested what you told me at your end ?

 

[HarrisM]

The lower example was assuming the number you are searching for is in E1. As you didn't want to use an inputbox I assumed the number would be in a cell. If that cell were E1 then each time you entered a new number jindon's code would run.

If that doesn't suit your needs then look at the first example, again assuming E1 is the source of the number to search for. You could change:

If Target.Value <> "" Then

to:

If Target.column = 2 Then

so the code will run each time you change a cell in column B.

 

[ayazgreat]

The codes give error at this line test (myWhat)
"overflow "Run time error 6"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
    myWhat = Target.Value
    [COLOR=red]test (myWhat)[/COLOR]
    End If
End Sub

 

[Sal Paradise]

You need to explain clearly with examples what you expect the code to do, what it is currently doing, and what the problem is.

If you keep repeating unclear instructions, you will not get help because people don't know how to help due to unclear requirements.

Rather than continually posting the same explanation, try to help people help you by giving them clear examples and expected results.

 

[ayazgreat]

I think this

Sub test(myWhat As Long)

should be

Sub test(myWhat As Variant)

And in my case both ranges uper and lower should be highlighted but the codes highlighted onlu lower range.

 

[ayazgreat]

Sal Paradise

Sorry I replied to HarrisM and I do not say to you to go back to go through all pages of this post to understand what the result I want to have because I know your time is precious.

 

[adeel.sid]

Another solution:
Generate a pivot table of one of your workshhet, then from 'filter label' in contain field and write number (within range)
it may works! but not sure!

Regards
Sid

 

[ayazgreat]

Sorry Pivot table is not a solution of my question.