VBA Error Code- 800A01A8 : Object required': 'Selection

Question

I am getting this error while running this vba code. its kind of syntax error. Please help me with that. here is the script. What script doing is , its copying data from one column to another and selecting the copied column.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Users\pc\Report\report_20120912.xls")
Set ws = objWorkbook.Worksheets("Data")
Set SelRange = objExcel.Selection
ws.Range("F2:F1000").AdvancedFilter 2,,ws.Range("M2"),True

ws.Range("N1") = ws.Range("A1")
ws.Range("O1") = ws.Range("B1")


//Below line has the bug

ws.Range("M2").Select             
selection=objExcel.Selection
col1=selection.End(xlDown)         //showing error on this line      
ws.Range(selection,col1).Select
n = ws.Range.Count
ws.Range("S2").Select

Why not totally take `Selection` out of your code? Directly work with the range? See this link http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select — Siddharth Rout, Oct 03 '12 at 09:01
@SiddharthRout : Thanks for you reply, would be good if you explain with an example. i went through the link provided by you above. What i want to do is selecting the column M from M2 cell till last say M1000. — workspace, Oct 03 '12 at 09:14
@SiddharthRout : Does the same code example which you provided works well with VBA also. — workspace, Oct 03 '12 at 09:29
I am sorry I do not understand what you mean? That is VBA Code. Are you doing this from VBA Excel? — Siddharth Rout, Oct 03 '12 at 09:31
@SiddharthRout:- Sorry i misunderstood something. It worked. Thanks a lot for your quick help. — workspace, Oct 03 '12 at 09:35

score 1 · Accepted Answer · edited May 23 '17 at 11:51

I see that you have tagged the question as Excel/Excel-VBA

If you are doing this in VBA Excel then you do not need to use CreateObject to create another instance of Excel. You can work with the current instance as well

See this code

Sub Sample()
    Dim objWorkbook As Workbook, ws As Worksheet

    Set objWorkbook = Workbooks.Open("C:\Users\pc\Report\report_20120912.xls")
    Set ws = objWorkbook.Worksheets("Data")

    With ws
        '
        '~~> Rest of the code
        '
    End With
End Sub

As mentioned in my comment, throw the Selection / Select out of your code. It not only slows down your code but can also give you runtime errors. See this link. I have commented the code below so that you will not have any problem understanding it. :)

Your above code can also be written as (I have not tested the code)

Option Explicit

Sub Sample()
    Dim objWorkbook As Workbook, ws As Worksheet
    Dim lRow As Long, n As Long
    Dim mRange As Range

    '~~> Open the workbook
    Set objWorkbook = Workbooks.Open("C:\Users\pc\Report\report_20120912.xls")
    '~~> Set the sheet that you want to work with
    Set ws = objWorkbook.Worksheets("Data")

    With ws
        .Range("F2:F1000").AdvancedFilter 2, , .Range("M2"), True

        .Range("N1").Value = .Range("A1").Value
        .Range("O1").Value = .Range("B1").Value

        '~~> Find the last row
        lRow = .Range("M" & .Rows.Count).End(xlUp).Row

        '~~> Set your range
        Set mRange = .Range("M2:M" & lRow)

        n = mRange.Count
    End With
End Sub

If you notice that you actually do not need to use .Select or work with the Selection

score 0 · Answer 2 · answered Oct 03 '12 at 08:57

0

The Selection property on Application is an object, so you need to use Set to assign it. Change

selection=objExcel.Selection

to

Set selection=objExcel.Selection

and the next line should work.

answered Oct 03 '12 at 08:57

AakashM

62,551
17
151
186

after putting the Set, its throwing the ""unknown runtime error. – workspace Oct 03 '12 at 09:00

score 0 · Answer 3 · answered Oct 03 '12 at 09:00

The following line is suspicious:

selection=objExcel.Selection

selection is probably supposed to be the global Selection propery (of Application). But you cannot assign to it; it's read-only. The spelling with the lower case s indicates that Excel is confused here. Furthermore, you would need to use Set to assign an object.

VBA Error Code- 800A01A8 : Object required': 'Selection

3 Answers3