Syntax error when attempting to select range

Question

The code below keeps giving me syntax error whenever I run to this line: Range(“SheetList[Worksheet Index]”).Select:

Private Sub Worksheet_Activate()

    Dim sheet As Object
    Dim SheetName As String

    On Error GoTo Error

    With Sheets("Index").ListObjects("SheetList")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With

    Range("SheetList[Worksheet Index]").Select      '<--------- syntax error

    For Each sheet In Sheets

        SheetName = sheet.Name

        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
                                   SubAddress:=SheetName & "!A1", _
                                   TextToDisplay:=SheetName

        ActiveCell.Offset(1, 0).Select

    Next sheet

    Exit Sub

Error:
    MsgBox "Unable to create index"

End Sub

I already created the SheetList table and Index sheet.

Can anyone help me figure this out?

Asking the same question as VBA: what is `SheetList[Worksheet Index]`? — paul bica, Sep 04 '15 at 17:34
Paul Bica is on the right track. What is it and how do we select it. I'm not sure selecting the range where it exists will do it. Why exactly are you trying to select that object. — MatthewD, Sep 04 '15 at 17:44
If `SheetList` is an Excel Table in the active sheet with a column header of `Worksheet Index` then the Range() selection should not fail. The OP code should work. I've tested this just now and it works here. — Excel Hero, Sep 04 '15 at 17:45

MatthewD · Answer 1 · 2015-09-04T17:45:02.587

0

Range is expecting Range(Column letter Row Number)
EX: Range("A1")

or

Range(Column letter Row Number : Column letter Row Number)
EX: Range("A1:B22")

There are some other ways to use Range. See here. https://msdn.microsoft.com/en-us/library/office/ff838238.aspx

What is SheetList[Worksheet Index] and how do we select it. I'm not sure selecting the range where it exists will do it. Why exactly are you trying to do by selecting that object?

edited Sep 04 '15 at 17:45

answered Sep 04 '15 at 17:34

MatthewD

6,719
5
22
41

1

OP - If you're trying to select all the cells on the `SheetList[WorksheetIndex]` sheet, then just add `.cells` in between: `Range("SheetList[Worksheet Index]").Cells.Select` (Edit: Although, looking at the code, you don't do anything with this line, so you could scrap it. Also, it's better to try to [avoid using `.Select` if you can](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros).) – BruceWayne Sep 04 '15 at 18:50

score 0 · Answer 2 · answered Sep 04 '15 at 23:37

You are trying to select something that you just deleted. Range("SheetList[Worksheet Index]") refers to the column under the Worksheet Index header in the .DataBodyRange of the SheetList structured table. You deleted it along with all other data a couple of lines earlier with .DataBodyRange.Delete. Did you mean instead to simply .ClearContents?

You need at least one row in the Range("SheetList").DataBodyRange if you are going to start putting new records in.

With Sheets("Index").ListObjects("SheetList")
    If Not .DataBodyRange Is Nothing Then
        .DataBodyRange.Delete
    End If
    .ListRows.Add  'leave at least one row to select below
End With

Range("SheetList[Worksheet Index]").Select      '<~~now it selects the empty row we created

If you moved the .ListRows.Add into your For Each...Next Statement as a preface for each new entry, you will not have to rely on .Select and ActiveCell to accomplish your goals.

Syntax error when attempting to select range

2 Answers2