Find a number in a list.

[Matt5353]

I have a list of numbers 0-8200. I can use the find function on the home tab but I wish to have a search box that will highlight the number selected but you have to scroll through the list to find it. Is there a way I can have a search box on my spreadsheet so when I type in the required number it will go straight to the number without scrolling through the long list of numbers?
Andrew

 

[Joe4]

You could create a little VBA procedure like this, and attach it to a button:

Sub MyFind()

    Dim x As Variant
    Dim rng As Range
    
'   Prompt user to enter value to look for
    x = InputBox("What value do you want to find?")
    
'   Search for value
    Set rng = Cells.Find(What:=x, After:=Range("A1"), LookIn:=xlFormulas2, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
        
'   Go to range
    If rng Is Nothing Then
        MsgBox "Cannot find " & x & " on the active sheet", vbOKOnly, "VALUE NOT FOUND!"
    Else
'       Select range
        rng.Select
'       Highlight range
        rng.Interior.Color = 65535
    End If
    
End Sub

 

[Matt5353]

You could create a little VBA procedure like this, and attach it to a button:

Sub MyFind()

    Dim x As Variant
    Dim rng As Range
   
'   Prompt user to enter value to look for
    x = InputBox("What value do you want to find?")
   
'   Search for value
    Set rng = Cells.Find(What:=x, After:=Range("A1"), LookIn:=xlFormulas2, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
       
'   Go to range
    If rng Is Nothing Then
        MsgBox "Cannot find " & x & " on the active sheet", vbOKOnly, "VALUE NOT FOUND!"
    Else
'       Select range
        rng.Select
'       Highlight range
        rng.Interior.Color = 65535
    End If
   
End Sub

So how does it actually work?

 

[Joe4]

What part don't you understand?

If you do not know how to add VBA code to your workbook, see here:

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

Then, if you do not know how to create a button and assign the VBA code to it, see here:

How To Add A Form Control Button To Run Your VBA Code | How To Excel

www.howtoexcel.org

 

[Matt5353]

So how does it actually work?

I pasted in the macro and it works up to the inserted box. When I reference what I want an error box 400 appears!!

 

[Joe4]

I pasted in the macro and it works up to the inserted box. When I reference what I want an error box 400 appears!!

I am not sure what you mean. Can you post some screen images of your data and errors?

 

[Matt5353]

Hope this is OK.
I put in a random number in the column and when I search it this box comes up

 

[Matt5353]

I hope you can understand the Image I up loaded?
Thanks for the help
Andrew

 

[Joe4]

I do not see any image in your post.

You should be able to use copy and paste images in your posts like this:

Also, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Matt5353]

I do not see any image in your post.

You should be able to use copy and paste images in your posts like this:

View attachment 56221

Also, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I have the XL2bb. I have never used it. I had a look and how do I get to attach my files to it so you can have it. I could always email you directly with my file.
Andrew