application.match question

[Guest]

--
--Chip Smith--
MVP Wannabe almost there but not quite. i'm using application.match in a
macro to find a
value inserted by the user in a userform. so the macro searches the
spreadsheet for the value and finds it. but what i want to do is when it
finds that value, it makes it the activecell...the macro kinda looks like
this -

Private Sub cmdAlt_Click()

Dim HFC As String
Dim Index As Variant
Dim nextrow As Long

HFC = Me.txtHFC1.Value

nextrow = Range("A65536").Row + 1

Index = Application.Match(HFC, Range("Sheet1!A1:A65536"), 0)

If IsError(Index) Then
MsgBox "Not Found, check HFC MAC and try again"
End If

Range(Index).Select

ActiveCell.Offset(0, 2).Value = Me.txtUser.Value
ActiveCell.Offset(0, 3).Value = Me.txtStat2.Value

Me.txtHFC1.Value = ""
Me.txtUser.Value = ""
Me.txtStat2.Value = ""
Me.txtHFC1.SetFocus

End Sub

 

[Dave Peterson]

You have another response to your other post.

--
--Chip Smith--
MVP Wannabe almost there but not quite. i'm using application.match in a
macro to find a
value inserted by the user in a userform. so the macro searches the
spreadsheet for the value and finds it. but what i want to do is when it
finds that value, it makes it the activecell...the macro kinda looks like
this -

Private Sub cmdAlt_Click()

Dim HFC As String
Dim Index As Variant
Dim nextrow As Long

HFC = Me.txtHFC1.Value

nextrow = Range("A65536").Row + 1

Index = Application.Match(HFC, Range("Sheet1!A1:A65536"), 0)

If IsError(Index) Then
MsgBox "Not Found, check HFC MAC and try again"
End If

Range(Index).Select

ActiveCell.Offset(0, 2).Value = Me.txtUser.Value
ActiveCell.Offset(0, 3).Value = Me.txtStat2.Value

Me.txtHFC1.Value = ""
Me.txtUser.Value = ""
Me.txtStat2.Value = ""
Me.txtHFC1.SetFocus

End Sub

 

[Bob Phillips]

If IsError(Index) Then
MsgBox "Not Found, check HFC MAC and try again"
Else
Worksheets("Sheet1").Cells(Index,"A").Select
End If


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)