software

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(quot;A65536quot;).Row 1

Index = Application.Match(HFC, Range(quot;Sheet1!A1:A65536quot;), 0)

If IsError(Index) Then
MsgBox quot;Not Found, check HFC MAC and try againquot;
End If

Range(Index).Select

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

Me.txtHFC1.Value = quot;quot;
Me.txtUser.Value = quot;quot;
Me.txtStat2.Value = quot;quot;
Me.txtHFC1.SetFocus

End Sub

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

quot;Chip Smithquot; wrote:

gt; almost there but not quite. i'm using application.match in a macro to find a
gt; value inserted by the user in a userform. so the macro searches the
gt; spreadsheet for the value and finds it. but what i want to do is when it
gt; finds that value, it makes it the activecell...the macro kinda looks like
gt; this -
gt;
gt; Private Sub cmdAlt_Click()
gt;
gt; Dim HFC As String
gt; Dim Index As Variant
gt; Dim nextrow As Long
gt;
gt; HFC = Me.txtHFC1.Value
gt;
gt; nextrow = Range(quot;A65536quot;).Row 1
gt;
gt; Index = Application.Match(HFC, Range(quot;Sheet1!A1:A65536quot;), 0)
gt;
gt; If IsError(Index) Then
gt; MsgBox quot;Not Found, check HFC MAC and try againquot;
gt; End If
gt;
gt; Range(Index).Select
gt;
gt; ActiveCell.Offset(0, 2).Value = Me.txtUser.Value
gt; ActiveCell.Offset(0, 3).Value = Me.txtStat2.Value
gt;
gt; Me.txtHFC1.Value = quot;quot;
gt; Me.txtUser.Value = quot;quot;
gt; Me.txtStat2.Value = quot;quot;
gt; Me.txtHFC1.SetFocus
gt;
gt; End Sub
gt;
gt; --
gt; --Chip Smith--
gt;

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(quot;sheet1quot;).range(quot;a:aquot;)

nextrow = Range(quot;A65536quot;).Row 1

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

If IsError(Index) Then
MsgBox quot;Not Found, check HFC MAC and try againquot;
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 = quot;quot;
Me.txtUser.Value = quot;quot;
Me.txtStat2.Value = quot;quot;
Me.txtHFC1.SetFocus

End Sub

Chip Smith wrote:
gt;
gt; almost there but not quite. i'm using application.match in a macro to find a
gt; value inserted by the user in a userform. so the macro searches the
gt; spreadsheet for the value and finds it. but what i want to do is when it
gt; finds that value, it makes it the activecell...the macro kinda looks like
gt; this -
gt;
gt; Private Sub cmdAlt_Click()
gt;
gt; Dim HFC As String
gt; Dim Index As Variant
gt; Dim nextrow As Long
gt;
gt; HFC = Me.txtHFC1.Value
gt;
gt; nextrow = Range(quot;A65536quot;).Row 1
gt;
gt; Index = Application.Match(HFC, Range(quot;Sheet1!A1:A65536quot;), 0)
gt;
gt; If IsError(Index) Then
gt; MsgBox quot;Not Found, check HFC MAC and try againquot;
gt; End If
gt;
gt; Range(Index).Select
gt;
gt; ActiveCell.Offset(0, 2).Value = Me.txtUser.Value
gt; ActiveCell.Offset(0, 3).Value = Me.txtStat2.Value
gt;
gt; Me.txtHFC1.Value = quot;quot;
gt; Me.txtUser.Value = quot;quot;
gt; Me.txtStat2.Value = quot;quot;
gt; Me.txtHFC1.SetFocus
gt;
gt; End Sub
gt;
gt; --
gt; --Chip Smith--

--

Dave Peterson

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 quot;Dave Petersonquot; wrote:

gt; Untested:
gt;
gt; Private Sub cmdAlt_Click()
gt;
gt; Dim HFC As String
gt; Dim Index As Variant
gt; Dim nextrow As Long
gt; Dim myRng as Range
gt;
gt; HFC = Me.txtHFC1.Value
gt;
gt; Set myRng = worksheets(quot;sheet1quot;).range(quot;a:aquot;)
gt;
gt; nextrow = Range(quot;A65536quot;).Row 1
gt;
gt; Index = Application.Match(HFC, myrng, 0)
gt;
gt; If IsError(Index) Then
gt; MsgBox quot;Not Found, check HFC MAC and try againquot;
gt; else
gt; myrng(index).Select
gt; 'if the worksheet isn't active, this might be better
gt; 'application.goto myrng(index)
gt; ActiveCell.Offset(0, 2).Value = Me.txtUser.Value
gt; ActiveCell.Offset(0, 3).Value = Me.txtStat2.Value
gt; end if
gt;
gt; Me.txtHFC1.Value = quot;quot;
gt; Me.txtUser.Value = quot;quot;
gt; Me.txtStat2.Value = quot;quot;
gt; Me.txtHFC1.SetFocus
gt;
gt; End Sub
gt;
gt; Chip Smith wrote:
gt; gt;
gt; gt; almost there but not quite. i'm using application.match in a macro to find a
gt; gt; value inserted by the user in a userform. so the macro searches the
gt; gt; spreadsheet for the value and finds it. but what i want to do is when it
gt; gt; finds that value, it makes it the activecell...the macro kinda looks like
gt; gt; this -
gt; gt;
gt; gt; Private Sub cmdAlt_Click()
gt; gt;
gt; gt; Dim HFC As String
gt; gt; Dim Index As Variant
gt; gt; Dim nextrow As Long
gt; gt;
gt; gt; HFC = Me.txtHFC1.Value
gt; gt;
gt; gt; nextrow = Range(quot;A65536quot;).Row 1
gt; gt;
gt; gt; Index = Application.Match(HFC, Range(quot;Sheet1!A1:A65536quot;), 0)
gt; gt;
gt; gt; If IsError(Index) Then
gt; gt; MsgBox quot;Not Found, check HFC MAC and try againquot;
gt; gt; End If
gt; gt;
gt; gt; Range(Index).Select
gt; gt;
gt; gt; ActiveCell.Offset(0, 2).Value = Me.txtUser.Value
gt; gt; ActiveCell.Offset(0, 3).Value = Me.txtStat2.Value
gt; gt;
gt; gt; Me.txtHFC1.Value = quot;quot;
gt; gt; Me.txtUser.Value = quot;quot;
gt; gt; Me.txtStat2.Value = quot;quot;
gt; gt; Me.txtHFC1.SetFocus
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; --
gt; gt; --Chip Smith--
gt;
gt; --
gt;
gt; Dave Peterson
gt;