Find and Select Cell based on cell value

ajogxb

New Member
Joined
Jul 21, 2016
Messages
7
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.

Code:
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
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top