How to use VBA to find/go to a row

[mike4]

I am working on a sheet with 100+ rows of data, each row representing a different size of widgets. I have a cell at the top - Q1 - where you can enter the size widget you are looking for and, using conditional formatting, the row(s) containing that size widget will turn yellow with bold text.

What I'd like to do is add a button that would allow the user to go immediately to that row without having to scroll up/down to try and find it. My VBA skill set is limited and I'm having trouble getting something to work.

Any help would be appreciated!

Thanks.

Mike

 

[Momentman]

Since you could have more than one row matching the criteria, what should happen when the button is clicked in such an instance?

 

[hippiehacker]

you can use

Sub Findwidget()

Dim rFound As Range



    On Error Resume Next

    With ActiveSheet
        Set rFound = .Columns(1).Find(What:=.Range("Q1"), LookIn:=xlValues, LookAt:=xlWhole) 'change .Columns(1) to the column number you need to look in
    On Error GoTo 0

        If Not rFound Is Nothing Then Application.Goto rFound, True

    End With
End Sub

 

[mike4]

Since you could have more than one row matching the criteria, what should happen when the button is clicked in such an instance?

All like size rows are sequential, so if it took the user to the first instance, all the others would be be immediately below it. I believe the greatest number of like-sized widgets is 4.

Thanks!

Mike

 

[Rick Rothstein]

Here is another macro for you to consider...

Sub FindQ1Cell()
  On Error GoTo NotThere
  Columns("A").Find(Range("Q1").Value, LookAt:=xlWhole, MatchCase:=False).Select
  Exit Sub
NotThere:
  MsgBox "That widget does not appear to exist!", vbCritical
End Sub

Change my guess at your column's letter designation to the actual letter designation where your widgets are listed.