Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

'Object Required' Error

Posted on 2007-11-16
14
Medium Priority
?
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 143

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
 
LVL 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

618 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