[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

'Object Required' Error

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
simondopickup
Asked:
simondopickup
  • 5
  • 4
  • 2
  • +2
1 Solution
 
Rory ArchibaldCommented:
Have you tried:

If Not delrng Is Nothing Then
    delrng.EntireColumn.Delete
End If

Open in new window

0
 
jeveristCommented:
Hi simondopickup,

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

Thanks, Rory!

Jim
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If Not delrng Is Nothing Then
needs to be

If Not (delrng Is Nothing) Then
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
simondopickupAuthor Commented:
I am still getting the error....
0
 
simondopickupAuthor Commented:
it is runtime error 424 - object required. I have tried all the suggestions sofar!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, then the error cannot occur on that line you showed us.
0
 
simondopickupAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jeveristCommented:
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
 
simondopickupAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
DrewKCommented:
Try simply adding:

Set delrng = Nothing

above the first line of the loop

DrewK
0
 
Rory ArchibaldCommented:
Good catch, AngelIII !
0
 
simondopickupAuthor Commented:
Indeed rorya - works fine now. thanks for the tip! Thanks to everyone
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now