Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can somebody check my script??
Hi
I have a button aon a worksheet that asks the user to select a 'job' to be printed. after input that number is searched for in column B of 'Thursday's log' and when this number is found the row of data is copied and pasted into the 'formula' worksheet. I thought I had this working, but I have copied the workbook onto another computer at work and it doesn't seem to work. Can anybody help, here is the script I have Sub print_thursdays_jobcard() Dim i As Integer Dim iRow As Integer Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long On Error GoTo err_handler Set wks1 = Worksheets("Thursday's log") Set wks2 = Worksheets("formula") Set wks3 = Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") iRow = Columns("B:B").Find _ (What:=i, _ After:=Range("B1"), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Row Worksheets("thursday's log").Activate ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _ :=Worksheets("formula").Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox "No job with the number " & i & " has been found, please try again!" End Sub thanks Anthony |
#2
|
|||
|
|||
Hi Anthony
A few things he Sometimes you use wks2, sometimes you use Worksheets("formula"). You don't sspecify which workbook these sheets are in. You assume that all errors are caused by the number not existing. Errors may be caused by renamed sheets, protected sheets, missing printer, ... An inputbox returns text. You should convert it to numeric and thereby also trap "Cancel". Here's my suggested first revision: Sub print_thursdays_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("Thursday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = Val(InputBox("Please enter the job number you wish to print a job card for")) If i = 0 Then Exit Sub On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) MsgBox "wks3.PrintOut" Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub HTH. Best wishes Harald "Anthony" skrev i melding ... Hi I have a button aon a worksheet that asks the user to select a 'job' to be printed. after input that number is searched for in column B of 'Thursday's log' and when this number is found the row of data is copied and pasted into the 'formula' worksheet. I thought I had this working, but I have copied the workbook onto another computer at work and it doesn't seem to work. Can anybody help, here is the script I have Sub print_thursdays_jobcard() Dim i As Integer Dim iRow As Integer Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long On Error GoTo err_handler Set wks1 = Worksheets("Thursday's log") Set wks2 = Worksheets("formula") Set wks3 = Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") iRow = Columns("B:B").Find _ (What:=i, _ After:=Range("B1"), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Row Worksheets("thursday's log").Activate ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _ :=Worksheets("formula").Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox "No job with the number " & i & " has been found, please try again!" End Sub thanks Anthony |
#3
|
|||
|
|||
Harald,
thanks for the help/info it seems to work - for now !! many thanks Anthony "Harald Staff" wrote: Hi Anthony A few things he Sometimes you use wks2, sometimes you use Worksheets("formula"). You don't sspecify which workbook these sheets are in. You assume that all errors are caused by the number not existing. Errors may be caused by renamed sheets, protected sheets, missing printer, ... An inputbox returns text. You should convert it to numeric and thereby also trap "Cancel". Here's my suggested first revision: Sub print_thursdays_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("Thursday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = Val(InputBox("Please enter the job number you wish to print a job card for")) If i = 0 Then Exit Sub On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) MsgBox "wks3.PrintOut" Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub HTH. Best wishes Harald "Anthony" skrev i melding ... Hi I have a button aon a worksheet that asks the user to select a 'job' to be printed. after input that number is searched for in column B of 'Thursday's log' and when this number is found the row of data is copied and pasted into the 'formula' worksheet. I thought I had this working, but I have copied the workbook onto another computer at work and it doesn't seem to work. Can anybody help, here is the script I have Sub print_thursdays_jobcard() Dim i As Integer Dim iRow As Integer Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long On Error GoTo err_handler Set wks1 = Worksheets("Thursday's log") Set wks2 = Worksheets("formula") Set wks3 = Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") iRow = Columns("B:B").Find _ (What:=i, _ After:=Range("B1"), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Row Worksheets("thursday's log").Activate ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _ :=Worksheets("formula").Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox "No job with the number " & i & " has been found, please try again!" End Sub thanks Anthony |
#4
|
|||
|
|||
Glad to hear that Anthony. Thanks for the feedback.
Best wishes Harald "Anthony" skrev i melding ... Harald, thanks for the help/info it seems to work - for now !! many thanks Anthony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a check box to accept a calculation | Excel Discussion (Misc queries) | |||
Auto spell check as in word | Excel Discussion (Misc queries) | |||
check box formatting | Excel Discussion (Misc queries) | |||
Using a Check Box as a control item | Excel Discussion (Misc queries) | |||
Creating a check box that does not require security clearance. | Excel Worksheet Functions |