CXomparing Job Numbers

[Guest]

The macro below is meant to go through a cell (Job Number) in A column,
compare it to B1:B17 and if the number doesn't exist, it will transfer the
number along with other details to Sheet 3, doesn't work- I'm clearly missing
something, I would greatly appreciate someone's help, thanks...


Sub mat()
For i = 1 To 10
If IsError(match(Cells(i, 1), "b1:b17", 0)) Then
Worksheets("Sheet1").Cells(i, 1).EntireRow.Copy
Destination:=Worksheets("Sheet3").Cells(i, 1)

End Sub

 

[Norman Jones]

Hi Teresa,

Try the following adaptation:

Sub mat()
Dim i As Long
Dim j As Long

j = 1
With Sheets("Sheet1")
For i = 1 To 10
If IsError(Application.Match(.Cells(i, 1), _
.Range("b1:b17"),
0)) Then
.Cells(i, 1).EntireRow.Copy _
Destination:=Worksheets("Sheet3").Cells(j,
1)
j = j + 1
End If
Next
End With
End Sub

 

[Norman Jones]

Hi Teresa,

Word has caused problems, so here is the code again:

Sub mat()
Dim i As Long
Dim j As Long

j = 1
With Sheets("Sheet1")
For i = 1 To 10
If IsError(Application.Match(.Cells(i, 1), _
.Range("b1:b17"), 0)) Then
.Cells(i, 1).EntireRow.Copy _
Destination:=Worksheets("Sheet3").Cells(j, 1)
j = j + 1
End If
Next
End With
End Sub