Object required error


G

Guest

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
 
Ad

Advertisements

Ad

Advertisements

G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top