Find and Select Cell based on cell value

[ajogxb]

My sheet contains hundreds of invoices that are separated by numbers in the first column.

ex. A B C D
1 Beginning of New Invoice
1 Body Of Invoice
1 End of First Invoice

2 Beginning of Second Invoice
2 Body Of Invoice
2 End of Second Invoice​

I want to be able to type 122 in say E1 and hit a button and then invoice 122 will be selected or just in view. I just cant exactly figure out the code for the button.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script will run when you enter a search value into Range("E1")
The script will search column A for the search value

No button required.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/20/2018  7:32:35 PM  EDT
If Not Intersect(Target, Range("E1")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim SearchRange As Range
Set SearchRange = Range("A1:A" & Lastrow).Find(Target.Value)
If SearchRange Is Nothing Then MsgBox "Seach value  " & Target.Value & "  Not found": Exit Sub
Application.Goto SearchRange
End If
End Sub

 

[Peter_SSs]

Another suggestion for a Worksheet_Change event code (that is, no need to 'hit a button' - the data will be presented as soon as the value is entered in E1). Installed as described in post 2, but this one behaves a little differently.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E1")) Is Nothing Then
    If IsEmpty(Range("E1").Value) Then
      If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    Else
      Intersect(ActiveSheet.UsedRange, Columns("A")).AutoFilter Field:=1, Criteria1:=Range("E1").Value
    End If
  End If
End Sub

 

[ajogxb]

Thank you. That works better than I initially wanted.

 

[My Aswer Is This]

Thank you. That works better than I initially wanted.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
I assume you used my script.

 

[Peter_SSs]

Thank you. That works better than I initially wanted.

Not sure which code you are referring to, but in any case I'm glad you got something that you are happy with.