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/