Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Number
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif number is greater than a number but less than another number | Excel Discussion (Misc queries) | |||
How can I click on a telephone number in an Excel 2002 spreadsheet, and have the number dialed? | Excel Discussion (Misc queries) | |||
How to calculate number of occurencies of a specific number number | Excel Discussion (Misc queries) | |||
Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions |