application.match
 

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

--
--Chip Smith--

Posting to this group
 

I am having trouble posting to this group. Can someone help me please?

"Chip Smith" wrote:

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

--
--Chip Smith--

application.match
 

Untested:

Private Sub cmdAlt_Click()

Dim HFC As String
Dim Index As Variant
Dim nextrow As Long
Dim myRng as Range

HFC = Me.txtHFC1.Value

Set myRng = worksheets("sheet1").range("a:a")

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

Index = Application.Match(HFC, myrng, 0)

If IsError(Index) Then
MsgBox "Not Found, check HFC MAC and try again"
else
myrng(index).Select
'if the worksheet isn't active, this might be better
'application.goto myrng(index)
ActiveCell.Offset(0, 2).Value = Me.txtUser.Value
ActiveCell.Offset(0, 3).Value = Me.txtStat2.Value
end if

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

End Sub

Chip Smith wrote:

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

--
--Chip Smith--

--

Dave Peterson

application.match
 

Thanks again Dave...yet agian you are the man! Been oh so very helpful in
many of my posts, especially this last project I've been working on. Sorry
for the multiple posts on the same topic...
--
--Chip Smith--
MVP Wannabe :)


"Dave Peterson" wrote:

Untested:

Private Sub cmdAlt_Click()

Dim HFC As String
Dim Index As Variant
Dim nextrow As Long
Dim myRng as Range

HFC = Me.txtHFC1.Value

Set myRng = worksheets("sheet1").range("a:a")

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

Index = Application.Match(HFC, myrng, 0)

If IsError(Index) Then
MsgBox "Not Found, check HFC MAC and try again"
else
myrng(index).Select
'if the worksheet isn't active, this might be better
'application.goto myrng(index)
ActiveCell.Offset(0, 2).Value = Me.txtUser.Value
ActiveCell.Offset(0, 3).Value = Me.txtStat2.Value
end if

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

End Sub

Chip Smith wrote:

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

--
--Chip Smith--

--

Dave Peterson