application.match question
 

--
--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

application.match question
 

You have another response to your other post.

Chip Smith wrote:

--
--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