Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object required error... | Excel Programming | |||
Object Required Error | Excel Programming | |||
Object Required error | Excel Programming | |||
Object Required Error | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |