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.

Private Sub CommandButton1_Click()

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

rngC, Cell and rw is declared in the calling sub, and is not passed as an
argument to the Function, which means that when you try and use it in the
Function, it will implicitly create a new object, that will be Nothing, so
the COUNTIF fails.

Also, your function has loads of arguments it doesn't use, lose them.

Add Option Explicit at the head of your code, it won't allow implicit
creation of variables, you have to explicitly declare them, avoiding these
pitfalls.

Apart from that, what is the Function supposed to do. The use of cell and
the range is confusing me.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"broogle" wrote in message
oups.com...
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.

Private Sub CommandButton1_Click()

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

From what I can see,
It is collecting a list of unique entries. It doesn't appear to have
arguments that aren't used as designed although some could be eliminated
(such as rw) but then additional code might be needed.

See my suggested rewrite.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...

It doesn't appear to have arguments that aren't used as designed ....

rngJ, rngK, rngL? Where are they used in the function?

For Each Cell In rngJ
Call mylabour
Next Cell

For Each Cell In rngK
Call mylabour
Next Cell

For Each Cell In rngL

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...

"Tom Ogilvy" wrote in message
...

It doesn't appear to have arguments that aren't used as designed ....

rngJ, rngK, rngL? Where are they used in the function?

But that is in the commandbutton sub, not the function, hence my point.

Bob


"Tom Ogilvy" wrote in message
...
For Each Cell In rngJ
Call mylabour
Next Cell

For Each Cell In rngK
Call mylabour
Next Cell

For Each Cell In rngL

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...

"Tom Ogilvy" wrote in message
...

It doesn't appear to have arguments that aren't used as designed ....

rngJ, rngK, rngL? Where are they used in the function?

Thanks Tom, It works!!!
And also thank to everyone who tried to help me. Happy Easter !

Private Sub CommandButton1_Click()

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(cell, rw, rngC)
Next Cell

For Each Cell In rngK
Call mylabour(cell, rw, rngC)
Next Cell

For Each Cell In rngL
Call mylabour(cell, rw, rngC)
Next Cell

End Sub

Function mylabour(cell As Range, rw as Long, rngC as Range)
If Cell < "xxx" Then
With Cell
If Application.CountIf(rngC, .Value) = 0 Then
Cells(rw, 3).Value = .Value
' a value was added, so increase or adjust size of
' rngC
Set rngC = Range(Cells(16, 3), Cells(Rows.Count, 3))
rw = rw + 1
End If

End With
End If
End Function

--
Regards,
Tom Ogilvy


"broogle" wrote in message
oups.com...
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.

Private Sub CommandButton1_Click()

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( cell, rw)
Next Cell

For Each Cell In rngK
Call mylabour cell, rw
Next Cell

For Each Cell In rngL
Call mylabour cell, rw
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