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, Jun 22 '16 at 13:45
[Don't use `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), 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, Jun 22 '16 at 13:49
When I do that I get "Run-Time error '381' Could not set the Column property. Invalid property array index." — Liz, Jun 22 '16 at 13:50
I have also tried the lined commented out I just posted. None of which worked — Liz, Jun 22 '16 at 13:52
I have successfully taken an array and placed it where I want in a newly created ws. I have also taken a range and placed it in the worksheet where I want it. I know that this much works. Now I want to take all of the data on that sheet (which will be a dynamic range - sometimes more columns/ sometimes more rows) and set it to a variable name for use later. — Liz, Jun 22 '16 at 14:03
what is Selected_Range, a range or an array? if an array, try removing the set, if a range, try removing the value. — Nathan_Sav, Jun 22 '16 at 14:04
Ultimately, later one I will use .Columns to set a ListBox equal to this variable name to populate the data into the listbox — Liz, Jun 22 '16 at 14:04

score 1 · Accepted Answer · edited May 23 '17 at 12:16

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 '17 at 12:16

Community

1
1

answered Jun 22 '16 at 14:03

Mathieu Guindon

69,817
8
107
235

I get Run-Time error '381' Could not set the Column property. Invalid property array index – Liz Jun 22 '16 at 14:08
@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 Jun 22 '16 at 14:10
*assuming you're getting that error on that specific line. Avoid problems, avoid working with `Selection`, see the linked post @BruceWayne gave you. – Mathieu Guindon Jun 22 '16 at 14:16
I am getting this error later on in my code. I will include more of my code above – Liz Jun 22 '16 at 14:25
@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. – Mathieu Guindon Jun 22 '16 at 14:28
I understand... very new to vba and this site. I will return it to it's original version. – Liz Jun 22 '16 at 14:31
Thank you all for your help! – Liz Jun 22 '16 at 14:33

Excel VBA Object Required Error

1 Answers1