Simple macro question

[bstratman]

This should be super easy, but I haven’t found an example and can’t figure it out on my own. here’s what I have…..

I have a list of names and addresses, sorted alphabetically by state. I have an input cell, C2, where the user is to enter the State they want to look up. Then I have a button they are to click. I want to make a macro where when they click the button it selects the first row that includes the State they entered and moved down the page to that row. Everything is on the same sheet, “Registration - 243”. The range for the State to lookup is H7:H223.

Can someone please help me with what I think should be a simple lookup/matching macro? Many thanks!

 

[iggydarsa]

Sub FindState()
    For i = 7 To 223
        If Range("H" & i).Value = Range("C2").Value Then
            Range("H" & i).Select
            Exit For
        End If
    Next i
End Sub

 

[bstratman]

Sub FindState()
    For i = 7 To 223
        If Range("H" & i).Value = Range("C2").Value Then
            Range("H" & i).Select
            Exit For
        End If
    Next i
End Sub

Thanks so much!!! Works perfectly

 

[bebo021999]

Alternative with no loop:

Sub findstate()
Range("H7:H223").Find(Range("C2")).Select
End Sub

 

[bstratman]

Alternative with no loop:

Sub findstate()
Range("H7:H223").Find(Range("C2")).Select
End Sub

Thanks again…simpler is always better! Many thanks to both of you!