Row Number
 

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

--
Message posted from http://www.ExcelForum.com

Row Number
 

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

--
Regards,
Tom Ogilvy

"bsmith3156 " wrote in message
...
I have a spreadsheet and am in the process of trying to create a button
that, once clicked, will search for an employee number (let's say
123456789) in Column A, then when it is found, will move to Column H
and post another number. The problem that I am having is that the
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 I
am not that proficient with VBA, nor am I completely finished with it
yet:


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

Bryan


---
Message posted from http://www.ExcelForum.com/

Row Number
 

Thanks, Tom! It works great!

Brya

--
Message posted from http://www.ExcelForum.com