Another Application Defined or Object Defined Error

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Greetings,

I am receiving an application or object defined error on the below line.

h is a range variable, AC is a string, k and b are integers

Code:
 COM.Worksheets(AC).Range(h, h.End(xlToRight)).Select

Code:
 For Each ws In COM.Worksheets    
    AC = ws.Name
    
    k = Cells(Rows.Count, "A").End(xlUp).Row
    
    Do Until k = 2
    
    If Cells(k, b) <> "" Then
 
    
        Set h = Cells(k, b)
        k = k - 1
          
        Else
    
        COM.Worksheets(AC).Range(h, h.End(xlToRight)).Select
        Selection.Copy
        COM.Worksheets(AC).Range(Cells(k, b)).PasteSpecial Paste:=xlPasteValues
        
        k = k - 1
        
        End If
        
        Loop
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
One part is not right. Since you did not show Set COM for a workbook, that would be my guess.
 
Upvote 0
There's at least a couple of things going wrong here ...

You can't select a range on another worksheet without first activating the sheet. But you actually don't need to use .Select in the first case. It's more efficient to:

Code:
'replace
COM.Worksheets(AC).Range(h, h.End(xlToRight)).Select
Selection.Copy
        
'with
COM.Worksheets(AC).Range(h, h.End(xlToRight)).Copy

You have unqualified references to Cells which will always refer to the ActiveSheet. It looks like you actually need:

k = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set h = ws.Cells(k, b)
etc

That should fix your object error. At the moment h is pointing to the ActiveSheet, so your code will error if you try to locate it on a different sheet:

COM.Worksheets(AC).Range(h, h.End(xlToRight))

Incidentally, you can replace Worksheets(AC) with ws.
 
Upvote 0
Try this.
Code:
For Each ws In COM.Worksheets    

    With ws
        k = .Cells(Rows.Count, "A").End(xlUp).Row
    
        Do Until k = 2
            If .Cells(k, b) <> "" Then
                Set h = .Cells(k, b)
                k = k - 1
           Else 
               .Range(h, h.End(xlToRight)).Copy
               .Cells(k, b).PasteSpecial Paste:=xlPasteValues
               k = k - 1
           End If
        
        Loop
 
Upvote 0
Sorry for the late reply,

Thanks for all the help! It seems to be running smoothly now, minus a glitch I have yet to investigate. using the above script design (the with ws statement) the script is going from worksheet to worksheet, but gets hung up on a seemingly random worksheet 10th of about 20 in the workbook and gives an error "range object of _worksheet failed". Any ideas? I will post the specific line highlighted at the error when I get more time in the next day or two. Thanks again!
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top