Can't idenify where Subscript out of range error is originating

Question

Whenever trying to select a variable range using a subroutine with parameters, I will also receive the error 9 Subscript out of range. If I remove the "ThisWorkBook.Worksheets("Sheet1")" I then receive a Gobal error.

I have tried using different routines such as Cells(x,y)

Sub Trail()

    Call SelectRow(2)

End Sub

Sub SelectRow(i As String)

    Dim theAddressA As String
    Dim theAddressL As String
    theAddressA = "A" & i
    theAddressL = "L" & i
    MsgBox (theAddressA) 'Prints "A2".
    MsgBox (theAddressL) 'Prints "L2".    
    ThisWorkbook.Worksheets("Sheet1").Range("theAddressA:theAddressL").Select

End Sub

I want to be able to select a row based on a variable input, as shown by the subroutine with the argument parameters. Ultimately once I can do this I'd like to compare individual rows with another table to then identify differences and similarities. Debug display image

Normally you don't need to `Select`, and probably should avoid it. See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for more detail. — BigBen, Aug 21 '19 at 01:08
Do you have a sheet named "Sheet1" in your workbook? Also - a sheet must be active before you can select cell(s) on it - but as mentioned, you probably don't need to `Select` at all. — BigBen, Aug 21 '19 at 01:12
It is specifically called "Affected APAC Truck Pop", I though the sheets name would be Sheet1 as shown in the properties and the name I assigned in the excel workbook would not be reference. However, changing it to the name I assigned resolved my issue. Thanks Ben, I will also look at the link to avoid using Select. Many Thanks. — Leo, Aug 21 '19 at 01:31
That is the codename. It is different from the actual sheet name. To use it, you would do `Sheet1.Range(theAddressA & ":" & theAddressL).Select`. But no problem, happy coding. — BigBen, Aug 21 '19 at 01:31

score 1 · Accepted Answer · answered Aug 21 '19 at 00:42

1

Dont put your variables in quotes... they are not literal strings. Instead, concatenate them like this.

ThisWorkbook.Worksheets("Sheet1").Range(theAddressA & ":" & theAddressL).Select

answered Aug 21 '19 at 00:42

braX

11,506
5
20
33

Thanks braX. However, I have tried this however, I am still getting the same "9" Subscript Error. – Leo Aug 21 '19 at 01:05
What does `MsgBox theAddressA & ":" & theAddressL` give you? Is there a sheet called "Sheet1" or is it called "Sheet 1"? – braX Aug 21 '19 at 01:10
'code' MsgBox theAddressA & ":" & theAddressL gives "A2:L2". the sheet is called Sheet1, more specifically "Affected APAC Truck Pop", changing it from Sheet1 to this resolved my issue! Thanks braX! – Leo Aug 21 '19 at 01:29

score 0 · Answer 2 · answered Aug 21 '19 at 01:34

0

I was referencing the name of the worksheet incorrectly, I thought that the name Sheet1 shown in the image would be what VBA references. However, it is the actual name which has been assigned that should be referenced. Thanks everyone

Name should be referenced not (Name)

answered Aug 21 '19 at 01:34

Leo

11
4

If you wanted to address the worksheet by index, you would do it like this: `ThisWorkbook.Worksheets(1)` – braX Aug 21 '19 at 02:09
`(Name)` is the name of VBA object and can be changed only in VBA editor. `Name` is the label displayed on sheet's tab. Instead of `ThisWorkbook.Worksheets("Affected APAC Truck Pop").Range(...` you can use `Sheet1.Range(...`. – BrakNicku Aug 21 '19 at 04:19

Can't idenify where Subscript out of range error is originating

2 Answers2