Permanent Search Box

[Robertson1995]

Hello. I have a sheet with 1500 items that are numbered 1-1500 in A3-A1503. I am always having to use the find (ctrl F) option to search for items by this number. The problem is it is only searching down so when I search for 123 for example it finds it, but then when I change the number to 111, it finds 1111. Is there a way to use VBA to make a cell a permanent search cell in A1 for example? So if I enter the number 123 in cell A1 and then run the macro it would start searching in Cell A3 and search down for the number. Then if I change the number to 111 instead of continuing to look down it would start the search again in cell A3? Thanks in advance for any help.

 

[Micron]

You should post the code relevant to your issue when mentioning that it's not working as you desire. Please post code between code tags (vba button on posting toolbar) to maintain indentation and readability. Perhaps you are using wild card(s) but I wouldn't speculate on that if I had read your code. Perhaps your code searches from the active cell when it should probably always start from A3.

 

[Robertson1995]

Hello. I currently don't have any code for this. Just wondering if what I need is possible.

 

[Skyybot]

Hello. I currently don't have any code for this. Just wondering if what I need is possible.

Yes. It is possible.

 

[Micron]

Sorry, I misinterpreted your post. Yes it's doable. With only 1500 rows, I don't think you'd need to worry about being more efficient with this. Question is, what would be the best user action (event) to fire the code? Your options (as I see them) would be worksheet change event or either double or right click.

Change event will fire no matter what cell gets changed on the sheet containing the code, so for this you test for the changed cell being A1.
BeforeRightClick, the code would test if the right click was on A1 and do nothing if not. Since you probably would not be right-clicking a whole lot, that would reduce running code unnecessarily a lot of the time.

Your choice.

 

[Skyybot]

Sorry, I misinterpreted your post. Yes it's doable. With only 1500 rows, I don't think you'd need to worry about being more efficient with this. Question is, what would be the best user action (event) to fire the code? Your options (as I see them) would be worksheet change event or either double or right click.

Change event will fire no matter what cell gets changed on the sheet containing the code, so for this you test for the changed cell being A1.
BeforeRightClick, the code would test if the right click was on A1 and do nothing if not. Since you probably would not be right-clicking a whole lot, that would reduce running code unnecessarily a lot of the time.

Your choice.

Funny. I was thinking of an entirely different approach. I was thing a subroutine linked via keyboard shortcut.

 

[shg]

In the Sheet module,

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cell As Range
 
  With Target
    If .Address = "$A$1" Then
      If VarType(.Value2) <> vbEmpty Then
        Set cell = Me.Range("A3", Cells(Rows.Count, "A")).Find(what:=.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If cell Is Nothing Then
          MsgBox Prompt:="Not found", Title:="Oops!", Buttons:=vbExclamation
        Else
          cell.Select
        End If
      End If
    End If
  End With
End Sub

The code will trigger when you change A1.

 

[Skyybot]

Assign to Ctrl+Shift+f or something. Could even replace the Ctrl+f if you wanted to.

Option Explicit

Sub SearchFor()
Dim startSearch As Range, srchFound As Range, srchFor As String
Set startSearch = ThisWorkbook.Sheets(1).Range("A1")
srchFor = InputBox("Enter the number to search for.", "Find")
Set srchFound = ThisWorkbook.Sheets(1).Range("A:A").Find(srchFor, startSearch, LookAt:=xlWhole)
If Not srchFound Is Nothing Then
    srchFound.Select
    Exit Sub
End If
End Sub