Solved

'Object Required' Error

Posted on 2007-11-16
14
235 Views
Last Modified: 2012-05-05
Experts,

I have this code thats is supposed to comapre 2 ranges and delete the columns of constituents on one range that doesnt appear in another. It is returning an 'Object required' error - can anyone tell me why?


Sub delete_fault()

' this sub deletes a  column in the OEE History Page when a fault is deleted from the userform. Only

' executes if there is data already written to the OEE History Sheet

Set OEEwkset = Worksheets("OEE History")

Set wksdata = Worksheets("DATA STORE")
 

'compare the new range aagainst current OEE History title range and delete entire columns from OEE HIstory that dont match.
 

If OEEwkset.Cells(1, 2).Value <> "" Then ' if data is recorded then..

    Set CLL = OEEwkset.Rows(1).Find _

    ("DOWNTIME", OEEwkset.Cells(1, OEEwkset.Columns.Count), xlValues, xlWhole)

    With OEEwkset

    Set rng_history = .Range(.Cells(1, 2), .Cells(1, CLL.Column - 1))

    End With

    Set rng = Range("FAULT_RANGE")

    

    

For Each cel In rng_history.Cells

    fndval = Application.Match(cel.Value, rng, 0)

    If IsError(fndval) Then

        If Not delrng Is Nothing Then

            If Intersect(delrng, cel.EntireColumn) Is Nothing Then

                Set delrng = Union(delrng, cel.EntireColumn)

            End If

        Else

            Set delrng = cel.EntireColumn

        End If

    End If

Next cel
 

OEEwkset.Activate

If Not delrng Is Nothing Then

    delrng.Select'<---------HITS ERROR HERE

    delrng.EntireColumn.Delete

End If

 

End If
 

End Sub

Open in new window

0
Comment
Question by:simondopickup
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20297942
Have you tried:

If Not delrng Is Nothing Then

    delrng.EntireColumn.Delete

End If

Open in new window

0
 
LVL 38

Expert Comment

by:jeverist
ID: 20297972
Hi simondopickup,

Sorry, I left that 'Select' in there by mistake.

Thanks, Rory!

Jim
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20298149
If Not delrng Is Nothing Then
needs to be

If Not (delrng Is Nothing) Then
0
 

Author Comment

by:simondopickup
ID: 20298643
I am still getting the error....
0
 

Author Comment

by:simondopickup
ID: 20298669
it is runtime error 424 - object required. I have tried all the suggestions sofar!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20298767
well, then the error cannot occur on that line you showed us.
0
 

Author Comment

by:simondopickup
ID: 20298814
If Not (delrngIsNothing) Then
    delrng.EntireColumn.Delete<------------------- error now hits here.
End If

The sheets OEE_wkset and wksdata are hidden - will this make a difference?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20298933
ok, please show this:


If Not (delrng Is Nothing) Then

    debug.print delrng.Address

    If Not (delrng.EntireColumn Is Nothing) Then

       debug.print delrng.EntireColumn.Address

       delrng.EntireColumn.Delete

    Else

       debug.print "Column not available"

    end if

Else

    debug.print "Range not set"

End If

Open in new window

0
 
LVL 38

Expert Comment

by:jeverist
ID: 20298995
simondopickup,

It looks like one of those lines has changed since you first posted it.  Change this:

If Not (delrngIsNothing) Then

back to this:

If Not delrng Is Nothing Then

Jim
0
 

Author Comment

by:simondopickup
ID: 20298996
The code returned an error again - it stopped at the arrow qoting 'object required'

If Not (delrng Is Nothing) Then
    Debug.Print delrng.Address<=====delrng.address=<'object required'>
    If Not (delrng.EntireColumn Is Nothing) Then
       Debug.Print delrng.EntireColumn.Address
       delrng.EntireColumn.Delete
    Else
       Debug.Print "Column not available"
    End If
Else
    Debug.Print "Range not set"
End If

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20299029
I "see" now the problem.
I assume you have NOT set the OPTION EXPLICIT, hence the variable is eventually not declared/initialized.
hence,  
If Not (delrng Is Nothing) Then
will return true, as delrng is, by default, a variant and not an object, ie not nothing.



I recommend that you add OPTION EXPLICIT to your modules, and declare all your variables appropriately.

add the single line as follows:
Sub delete_fault()
 

dim delrng as Range
 

' this sub deletes a  column in the OEE History Page when a fault is deleted from the userform. Only

' executes if there is data already written to the OEE History Sheet

Set OEEwkset = Worksheets("OEE History")

Set wksdata = Worksheets("DATA STORE")

 

'compare the new range aagainst current OEE History title range and delete entire columns from OEE HIstory that dont match.

 

If OEEwkset.Cells(1, 2).Value <> "" Then ' if data is recorded then..

    Set CLL = OEEwkset.Rows(1).Find _

    ("DOWNTIME", OEEwkset.Cells(1, OEEwkset.Columns.Count), xlValues, xlWhole)

    With OEEwkset

    Set rng_history = .Range(.Cells(1, 2), .Cells(1, CLL.Column - 1))

    End With

    Set rng = Range("FAULT_RANGE")

    

    

For Each cel In rng_history.Cells

    fndval = Application.Match(cel.Value, rng, 0)

    If IsError(fndval) Then

        If Not delrng Is Nothing Then

            If Intersect(delrng, cel.EntireColumn) Is Nothing Then

                Set delrng = Union(delrng, cel.EntireColumn)

            End If

        Else

            Set delrng = cel.EntireColumn

        End If

    End If

Next cel

 

OEEwkset.Activate

If Not delrng Is Nothing Then

    delrng.EntireColumn.Delete

End If

 

End If

 

End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:DrewK
ID: 20299230
Try simply adding:

Set delrng = Nothing

above the first line of the loop

DrewK
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20299293
Good catch, AngelIII !
0
 

Author Comment

by:simondopickup
ID: 20299899
Indeed rorya - works fine now. thanks for the tip! Thanks to everyone
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now