Excel VBA Object Required Error

Question

I have been looking at this code and can't seem to figure out why I keep getting an object required error. I am trying to add a new sheet, place and array and range in the sheet (this works). Next I want to name all of the cells on the sheet a variable name to be used later. Can anyone see why it's not working?

    Set WS_Temp = Sheets.Add
With WS_Temp
    .Range(Cells(1, 1), Cells(1, LastColRA)) = Sheet1.Range("Dynamic_Range").Value
    .Range(Cells(2, 1), Cells(counter + 1, LastColRA)) = Application.Transpose(vList)
    '.Range(Cells(1, 1), Cells(counter + 1, LastColRA)) = Selected_Range
    '.Range(Selection, Selection.SpecialCells(xlLastCell)).Select = Selected_Range
End With

Set Selected_Range = WS_Temp.Range(Selection, Selection.SpecialCells(xlLastCell)).Value ***ERRORS HERE

I have Dimed WS_Temp as Worksheet and Selected_Range As Range earlier.. FYI — Liz
– Liz, Commented Jun 22, 2016 at 13:45
Don't use .Select, that's likely part of it. Also, don't use Value at the end. You just set the Range(), then if you need the value of the range, do Selected_Range.Value. — BruceWayne
– BruceWayne, Commented Jun 22, 2016 at 13:49
When I do that I get "Run-Time error '381' Could not set the Column property. Invalid property array index." — Liz
– Liz, Commented Jun 22, 2016 at 13:50
I have also tried the lined commented out I just posted. None of which worked — Liz
– Liz, Commented Jun 22, 2016 at 13:52

Community · Accepted Answer · 2017-05-23 12:16:13Z

1

Can you try taking off the .Value? – Matt Cremeens

The Set keyword is used to assign object references. Using it to assign values throws the error you're getting, "Object required".

And that's exactly what you're doing here:

Set Selected_Range = WS_Temp.Range(Selection, Selection.SpecialCells(xlLastCell)).Value

You're trying to assign the Selected_Range object the value of WS_Temp.Range(...), which you cannot do legally. Remove .Value and you will assign Selected_Range a reference pointing to a Range object returned by that Range call on your WS_Temp sheet.

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Jun 22, 2016 at 14:03

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

7 Comments

Liz Over a year ago

I get Run-Time error '381' Could not set the Column property. Invalid property array index

Mathieu Guindon Over a year ago

@Liz no repro here, perhaps Selection needs to be validated first? works fine here. Range is a parameterized Property Get member of Worksheet; getting that error means you're giving it invalid parameters. Verify that Selection is in the WS_Temp sheet.

Mathieu Guindon Over a year ago

*assuming you're getting that error on that specific line. Avoid problems, avoid working with Selection, see the linked post @BruceWayne gave you.

Liz Over a year ago

I am getting this error later on in my code. I will include more of my code above

Mathieu Guindon Over a year ago

@Liz ok, so the specific issue you asked about in this question is fixed, right? This is a Q&A site, not a discussion forum. People will google "vba object required" and land here and find what they're looking for. Please don't edit your question to add new problems every time one gets answered, that's not how this site works. Chameleon questions are very much frowned upon. Thank you.

|

Collectives™ on Stack Overflow

Excel VBA Object Required Error

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related