VBA code with Object required error

Question

Code has error. When I debug, it shows the error of last line.

Sub test()
    WB_Master = ActiveWorkbook.Name
    Dim ra As Range
    open file
    Workbooks.Open FileName:="X:\Projects\RPOC\Comparison\book1.xlsx"
    WB_Source = ActiveWorkbook.Name
    Workbooks(WB_Source).Activate
    Worksheets("sheet1").Activate
    ' set value to ra. Is it correct?
    Set ra = Range("c2")

    Workbooks(WB_Source).Close SaveChanges:=False


    Workbooks(WB_Master).Activate

    Worksheets("sheet1").Activate

    Set Range("k2").Value = ra.Value

End Sub

YowE3K · Accepted Answer · 2017-07-09 23:07:02Z

1

You can't Set a Value - you should only use the Set keyword when assigning an object reference. (E.g. your Set ra = Range("c2") is assigning a reference to Range("c2") to your object ra.)

So change

Set Range("k2").Value = ra.Value

to

Range("k2").Value = ra.Value

Because you are also closing the workbook that contains the range referred to by your ra variable before you use it, you will also have problems. I have refactored your code to get around that issue:

Sub test()
    Dim WB_Source As Workbook
    Dim WB_Master As Workbook

    Set WB_Master = ActiveWorkbook
    Set WB_Source = Workbooks.Open(FileName:="X:\Projects\RPOC\Comparison\book1.xlsx")

    WB_Master.Worksheets("sheet1").Range("k2").Value = _
        WB_Source.Worksheets("sheet1").Range("c2").Value

    WB_Source.Close SaveChanges:=False
End Sub

(Note: I changed your WB_Source and WB_Master variables from being Variant/String to being Workbook.)

edited Jul 9, 2017 at 23:07

answered Jul 9, 2017 at 22:25

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

L42 Over a year ago

I think it is assigning Range("c2") object reference to a variable range ra.

YowE3K Over a year ago

@zhenhao Ohhh - You are closing the workbook first! I didn't notice that. Just a second and I will update the answer.

zhenhao Over a year ago

@YowE3K By the way, if I use this method to read data from several files ( about 500), which have the same format, +9will it be slow?

YowE3K Over a year ago

@zhenhao Yes, to open 500 files and read a single value from each one will be slow. You should consider whether your data structure (one important number in 500 workbooks) is appropriate for what you are doing. (If you have 500 offline users, each with their own workbook, and they each send you a copy of their file and you are then trying to create a summary - maybe it is the best way. But I can't think of many other scenarios where it would make sense.)

Collectives™ on Stack Overflow

VBA code with Object required error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related