Macro script error - pls help !!

[Guest]

Hi,
A button on worksheet1 (mondays log) is selected by the user and he/she is
then asked which job they wish to print - on input that number is searched
for in column B of Monday's log and the contents of that row are copied and
pasted into row A2 of sheet2 (Formula).
The script I have been given below works almost perfectly apart from the
following errors.

in 'monday's log' row B8 has job number 1, row B9 has job number 23 and the
rest of the rows are blank (for the moment)

The user inputs job 1 to print outcome is perfect
The user inputs job 2 to print and job 23 is picked up and printed
The user inputs job 3 to print and job 23 is picked up and printed
The user inputs job 4 to print and 'zero' values are picked up and printed
any other number entered by the user works fine (ie the error is shown if no
job with the input number is found).

I don't understand why when job 2 is selected number 23 is selected and the
same for job 3, or why if job 4 is selected I get a page of 'zero' values

Can anybody solve this for me

here is the script I was given

Sub print_mon_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("monday'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:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
Worksheets("monday's log").Activate
ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
:=Worksheets("formula").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
MsgBox "No job with the number " & i & " has been found, please try
again!"
End Sub

Many many thanks for any help

Anthony

 

[Dave O]

Your code is currently structured to find the first 2 or 3 in the
search area- when it looks for 2 or 3 the first one encountered happens
to be the 23 entry. You can solve this by taking these steps:
1. MAKE A BACKUP OF YOUR DATA so you don't inadvertenly lose critical
information, and so you can revert to your original (albeit somewhat
flawed) version.
2. Where your code says

LookIn:=xlFormulas, _
LookAt:=xlPart, _

.... replace these lines with
LookIn:=xlValues, _
LookAt:=xlWhole, _

This causes the search to look for just the value 2 or just the value 3
and will not find the value 23, for instance.

See if the solves the zero values with option 4 problem- I don't have
an idea for that one, but it may be something specific to your data
rather than the code.

Cheers!!

 

[Dave Peterson]

Maybe it's as simple as changing:
LookAt:=xlPart,
to
LookAt:=xlWhole,

If you want to look at the complete cell's value.

 

[Guest]

Dave,
thanks that seems to have done the trick - easy if you know how!!
Cheers
Anthony