Error handling in this code

[L. Howard Kittle]

Hello Excel users and Experts,
Excel 2002 SP3

I'm stumped on error handling in this code. If there is no number entered
in the Input box I get a type mismatch error message. Have tried "If j is
nothing exit sub", and "on error resume next" but it keeps slapping me
around! If you enter a number that is not in the list in column A then I
don't get the error but want to do a message box saying the number did not
exist in Col A. It's kicking my butt on that too!

Sub TransferIt()
Dim i As Long
Dim Rng As Range
Dim c As Range
Dim j As Long

i = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A2:A" & i)
j = InputBox("Please enter the job" & vbCr & _
"number you wish to" & vbCr & "print a job card for")

For Each c In Rng
If c.Value = j Then
c.Resize(1, 7).Copy Sheets("Sheet2"). _
Range("G100").End(xlUp).Offset(1, 0)
End If
Next
End Sub

Thanks for your help.
Regards,
Howard

 

[Dave Peterson]

One way to get a numeric value back is to use application.inputbox. Then you
don't have to do as much validation to check what the user typed.

And you could use application.match to see if that number entered matched your
range. And if it did match, then you could just copy that cell (after resizing
it):

Option Explicit

Sub TransferIt()
Dim i As Long
Dim Rng As Range
Dim c As Range
Dim j As Long
Dim res As Variant

i = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A2:A" & i)
j = CLng(Application.InputBox("Please enter the job" & vbCr & _
"number you wish to" & vbCr & "print a job card for", Type:=1))

If j = 0 Then
'user hit cancel
Exit Sub '??
End If

res = Application.Match(j, Rng, 0)

If IsError(res) Then
MsgBox "Not found!"
Else
Rng(res).Resize(1, 7).Copy _
Destination:=Worksheets("Sheet2").Range("G100").End(xlUp).Offset(1, 0)
End If

End Sub

=========
One difference. Your code loops through all the cells in that Rng variable. If
you have duplicates job numbers, then your code will copy both duplicate ranges.

The code I suggested just does the first match--and then quits.

I'm not sure if you have duplicates -- if you don't, then that difference won't
be important.

 

[L. Howard Kittle]

Hi Dave,

Actually there could be more than one number in column A and I want to copy
all of those same number's row to the other sheet. So if there is three
1234's then all three would be copied to the other sheet, one below the
other.

The error stuff seems to be okay.

Thanks for the good info and guidance.

Thanks,
Regards,
Howard

 

[L. Howard Kittle]

Hi again Dave,

I don't think I fully understood your 'splanation after first and second
review. I will muck about and try to incorporate your advice into this
code. I'm vauge but have a mild sense about what you are talking about.
May need more help in the neart future.

This is in response to an "Anthony" post dated 10/7/2005 8:54 AM titled
"Macro VB code help" in Excel.misc.

He wants all version of the lookup number to be copied to new sheet.

Thanks,
Howard

 

[Dave Peterson]

There's an example in VBA's help how to use Find to search through a range. It
keeps track of the first address that was found and then repeats the find until
the found cell addess matches that first address.

I would think that that technique would be quicker than looping through the
cells in the range.