Object required error
 

I received object required error on the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range


Set rng = Range("$A$30:$A$10000")

With rng
DuplPack rng
End With
End Sub
------------------------------------------------------------------------------------------------
Sub DuplPack(rng As Range)

Dim Found As Range
Dim duplicates As Long
Dim a As Range
'On Error GoTo ErrHandler
If Intersect(Target, rng) Is Nothing Then Exit Sub 'This is the line
that gave me the error

Application.EnableEvents = False

duplicates = Application.CountIf(rng, Target.Value)

If Trim(Target.Value) < "" Then

If duplicates 1 Then

Set Found = rng.Find(Target.Value)

If Found.Address = Target.Address Then Set Found =
rng.FindNext(Target)

If Found.Address < Target.Address Then
MsgBox "The same Manufacturing Lot Number " & Target.Value &
" was found in cell" & Found.Address & " and cell " & Target.Address,
vbOKOnly, "Duplicate Manufacturing Lot Number"
Target.Activate
Target.ClearContents
End If

End If
End If

Set Found = Nothing
Set rng = Nothing

Application.EnableEvents = True
Exit Sub

'ErrHandler:

' MsgBox "An error occurred: " & CStr(Err.Number) & vbCrLf & _
' "Description: " & Err.Description & vbCrLf & vbCrLf & _
' "Copy or print error message and contact your system administrator."

End Sub

Object required error
 

Target is not defined
--
Gary''s Student - gsnu200717


"anamarie30" wrote:

I received object required error on the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range


Set rng = Range("$A$30:$A$10000")

With rng
DuplPack rng
End With
End Sub
------------------------------------------------------------------------------------------------
Sub DuplPack(rng As Range)

Dim Found As Range
Dim duplicates As Long
Dim a As Range
'On Error GoTo ErrHandler
If Intersect(Target, rng) Is Nothing Then Exit Sub 'This is the line
that gave me the error

Application.EnableEvents = False

duplicates = Application.CountIf(rng, Target.Value)

If Trim(Target.Value) < "" Then

If duplicates 1 Then

Set Found = rng.Find(Target.Value)

If Found.Address = Target.Address Then Set Found =
rng.FindNext(Target)

If Found.Address < Target.Address Then
MsgBox "The same Manufacturing Lot Number " & Target.Value &
" was found in cell" & Found.Address & " and cell " & Target.Address,
vbOKOnly, "Duplicate Manufacturing Lot Number"
Target.Activate
Target.ClearContents
End If

End If
End If

Set Found = Nothing
Set rng = Nothing

Application.EnableEvents = True
Exit Sub

'ErrHandler:

' MsgBox "An error occurred: " & CStr(Err.Number) & vbCrLf & _
' "Description: " & Err.Description & vbCrLf & vbCrLf & _
' "Copy or print error message and contact your system administrator."

End Sub

Object required error
 

You need to give Target a Value I think

"anamarie30" wrote:

I received object required error on the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range


Set rng = Range("$A$30:$A$10000")

With rng
DuplPack rng
End With
End Sub
------------------------------------------------------------------------------------------------
Sub DuplPack(rng As Range)

Dim Found As Range
Dim duplicates As Long
Dim a As Range
'On Error GoTo ErrHandler
If Intersect(Target, rng) Is Nothing Then Exit Sub 'This is the line
that gave me the error

Application.EnableEvents = False

duplicates = Application.CountIf(rng, Target.Value)

If Trim(Target.Value) < "" Then

If duplicates 1 Then

Set Found = rng.Find(Target.Value)

If Found.Address = Target.Address Then Set Found =
rng.FindNext(Target)

If Found.Address < Target.Address Then
MsgBox "The same Manufacturing Lot Number " & Target.Value &
" was found in cell" & Found.Address & " and cell " & Target.Address,
vbOKOnly, "Duplicate Manufacturing Lot Number"
Target.Activate
Target.ClearContents
End If

End If
End If

Set Found = Nothing
Set rng = Nothing

Application.EnableEvents = True
Exit Sub

'ErrHandler:

' MsgBox "An error occurred: " & CStr(Err.Number) & vbCrLf & _
' "Description: " & Err.Description & vbCrLf & vbCrLf & _
' "Copy or print error message and contact your system administrator."

End Sub

Object required error
 

anamarie,

Your DuplPack subroutine will not recognize Target.

try changing the declaration to become

Sub DuplPack(rng As Range, Target as Range)

Then, call it as:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("$A$30:$A$10000")
DuplPack rng, Target
End Sub




--
Hope that helps.

Vergel Adriano


"anamarie30" wrote:

I received object required error on the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range


Set rng = Range("$A$30:$A$10000")

With rng
DuplPack rng
End With
End Sub
------------------------------------------------------------------------------------------------
Sub DuplPack(rng As Range)

Dim Found As Range
Dim duplicates As Long
Dim a As Range
'On Error GoTo ErrHandler
If Intersect(Target, rng) Is Nothing Then Exit Sub 'This is the line
that gave me the error

Application.EnableEvents = False

duplicates = Application.CountIf(rng, Target.Value)

If Trim(Target.Value) < "" Then

If duplicates 1 Then

Set Found = rng.Find(Target.Value)

If Found.Address = Target.Address Then Set Found =
rng.FindNext(Target)

If Found.Address < Target.Address Then
MsgBox "The same Manufacturing Lot Number " & Target.Value &
" was found in cell" & Found.Address & " and cell " & Target.Address,
vbOKOnly, "Duplicate Manufacturing Lot Number"
Target.Activate
Target.ClearContents
End If

End If
End If

Set Found = Nothing
Set rng = Nothing

Application.EnableEvents = True
Exit Sub

'ErrHandler:

' MsgBox "An error occurred: " & CStr(Err.Number) & vbCrLf & _
' "Description: " & Err.Description & vbCrLf & vbCrLf & _
' "Copy or print error message and contact your system administrator."

End Sub