Row Number

[bsmith3156]

I have a spreadsheet and am in the process of trying to create a butto
that, once clicked, will search for an employee number (let's sa
123456789) in Column A, then when it is found, will move to Column
and post another number. The problem that I am having is that th
Row() function keeps giving me a "Sub or function not defined" error.
Following is the code I have done, and it may be inefficient, because
am not that proficient with VBA, nor am I completely finished with i
yet:


Sub CommandButton1_Click()
Dim SSN As Long
Dim Y As Long

Application.Workbooks.Open ("\\file2c\shared\evaluations\Score
Spreadsheet.xls"), Notify _
:=False

Workbooks(1).Activate
'Cells(4, 7).Select
SSN = Cells(4, 7).Value
Workbooks(2).Activate
ActiveSheet.Columns("A:A").Select
Selection.Find(What:=SSN, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
_
MatchCase:=False).Activate
Workbooks(1).Activate
'Range("Final").Select
Y = Range("Final").Value
Workbooks(2).Activate
Row = Row(SSN)
ActiveSheet.Cells(Row, 8).Select

ActiveWorkbook.SaveAs FileName:= _
"\\file2c\shared\Evaluations\Completed\2004\" & Cells(4
7).Value & " " & Cells(3, 5) & " - AnnualPMTT 2004 "
FileFormat:=xlNormal _
, Password:="", WriteResPassword:=""
ReadOnlyRecommended:=False, _
CreateBackup:=False

iAnswer = MsgBox("Save is finished", vbOKOnly)
If iAnswer = vbOK Then Exit Sub
End Sub


Can someone help?

Thanks,

Brya

 

[Tom Ogilvy]

Sub CommandButton1_Click()
Dim SSN As Long
Dim Y As Long

Application.Workbooks.Open ("\\file2c\shared\evaluations\Scores
Spreadsheet.xls"), Notify _
:=False

Workbooks(1).Activate
'Cells(4, 7).Select
SSN = Cells(4, 7).Value
Workbooks(2).Activate
ActiveSheet.Columns("A:A").Select
Selection.Find(What:=SSN, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate
set rng = ActiveCell
Workbooks(1).Activate
'Range("Final").Select
Y = Range("Final").Value
Workbooks(2).Activate
lRow = rng.row
ActiveSheet.Cells(lRow, 8).Select
' selection = y ' do you want to assign a value here?
ActiveWorkbook.SaveAs FileName:= _
"\\file2c\shared\Evaluations\Completed\2004\" & Cells(4,
7).Value & " " & Cells(3, 5) & " - AnnualPMTT 2004 ",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

iAnswer = MsgBox("Save is finished", vbOKOnly)
If iAnswer = vbOK Then Exit Sub
End Sub

 

[bsmith3156]

Thanks, Tom! It works great!

Brya