For Each Cell In rngA
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


For Each Cell In rngB
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


For Each Cell In rngC
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


******** Thanks !!! ********

Why do you want it to involve an array?

Consider the following untested alternative:

Set rngAll=Union(rngA, rngB, rngC)
for each iCell in rngAll
If iCell.Value < "xxx" Then
If Application.Countif(rngZ,iCell.Value)=0 Then
Cells(rw,3).Value=iCell.Value
rw=rw+1
End If
End if
Next iCell

Alan Beban

broogle wrote:
For Each Cell In rngA
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


For Each Cell In rngB
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


For Each Cell In rngC
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


******** Thanks !!! ********

In a later posting, he stated that the ranges are on different sheets which
for the benefit of the OP only, would obviate use of Union. He also
received some good suggestions to use a collection as per John Walkenbach's
site, so you might want to suggest the dictionary alternative <g.

--
Regards,
Tom Ogilvy

"Alan Beban" wrote in message
...
Why do you want it to involve an array?

Consider the following untested alternative:

Set rngAll=Union(rngA, rngB, rngC)
for each iCell in rngAll
If iCell.Value < "xxx" Then
If Application.Countif(rngZ,iCell.Value)=0 Then
Cells(rw,3).Value=iCell.Value
rw=rw+1
End If
End if
Next iCell

Alan Beban

broogle wrote:
For Each Cell In rngA
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


For Each Cell In rngB
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


For Each Cell In rngC
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngZ, .Value) = 0 Then

Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

Next Cell


******** Thanks !!! ********

Tom Ogilvy wrote:
In a later posting, he stated that the ranges are on different sheets which
for the benefit of the OP only, would obviate use of Union. He also
received some good suggestions to use a collection as per John Walkenbach's
site, so you might want to suggest the dictionary alternative <g.

Hey, that's enough of a suggestion to aim someone toward the dictionary
alternative. Without seeing the code involving the collection I can't
really tell if a Dictionary Object would present any advantage.

I agree, of course, on the Union issue. I could probably suggest
similarly simple code involving the MakeArray function rather than
Union, but the OP seems to have enough on his plate already.

Alan Beban

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Guys, I try to use function to solve my problem but I keep getting
"Object required' error. Could you help me to fix this problem?
Thank you for all your help.

Dim shtJ As Worksheet
Dim shtK As Worksheet
Dim shtL As Worksheet
Dim rngC As Range
Dim rngE As Range
Dim rngJ As Range
Dim rngK As Range
Dim rngL As Range
Dim Cell As Range
Dim rw As Long

Set shtJ = Worksheets("Labour A")
Set shtK = Worksheets("Labour B")
Set shtL = Worksheets("Labour C")


Set rngC = Range(Cells(16, 3), Cells(Rows.Count, 3))
Set rngJ = shtJ.Range(shtJ.Cells(6, 5), shtJ.Cells(86, 5))
Set rngK = shtK.Range(shtK.Cells(6, 4), shtK.Cells(86, 4))
Set rngL = shtL.Range(shtL.Cells(6, 5), shtL.Cells(86, 5))
rw = 16
rngC.Clear

For Each Cell In rngJ
Call mylabour
Next Cell

For Each Cell In rngK
Call mylabour
Next Cell

For Each Cell In rngL
Call mylabour
Next Cell

End Sub

Function mylabour(rngJ, rngK, rngL As Range) As Range

If Cell < "xxx" Then
With Cell
If Application.CountIf(rngC, .Value) = 0 Then
Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

End Function

You have received a suggestion at your later posting in a new thread.

--
Regards,
Tom Ogilvy

"broogle" wrote in message
oups.com...
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Guys, I try to use function to solve my problem but I keep getting
"Object required' error. Could you help me to fix this problem?
Thank you for all your help.

Dim shtJ As Worksheet
Dim shtK As Worksheet
Dim shtL As Worksheet
Dim rngC As Range
Dim rngE As Range
Dim rngJ As Range
Dim rngK As Range
Dim rngL As Range
Dim Cell As Range
Dim rw As Long

Set shtJ = Worksheets("Labour A")
Set shtK = Worksheets("Labour B")
Set shtL = Worksheets("Labour C")


Set rngC = Range(Cells(16, 3), Cells(Rows.Count, 3))
Set rngJ = shtJ.Range(shtJ.Cells(6, 5), shtJ.Cells(86, 5))
Set rngK = shtK.Range(shtK.Cells(6, 4), shtK.Cells(86, 4))
Set rngL = shtL.Range(shtL.Cells(6, 5), shtL.Cells(86, 5))
rw = 16
rngC.Clear

For Each Cell In rngJ
Call mylabour
Next Cell

For Each Cell In rngK
Call mylabour
Next Cell

For Each Cell In rngL
Call mylabour
Next Cell

End Sub

Function mylabour(rngJ, rngK, rngL As Range) As Range

If Cell < "xxx" Then
With Cell
If Application.CountIf(rngC, .Value) = 0 Then
Cells(rw, 3).Value = .Value
rw = rw + 1
End If

End With
End If

End Function