Loop but Subscript out of Range

[Opv]

Could someone please tell me why I am getting an Error 9 (Subscript out of range) with the following loop?



sub createHeadingNames()

Call getCols

Sheets("History)").Activate

Application.screenUpdating = False

Dim goatHeadings As Variant
Dim i As Integer

goatHeadings = Range(headingstartRng, Range(headingstartRng).End(xlToRight)).Value

For i = 1 To currentCols
Cells(2, i).Name = goatHeadings(i)
Next

Application.screenUpdating = True

End Sub

[Bob Phillips]

You don't give us much do you?

It could be many things:

- there is no sheet called History (in fact you can't have a sheet called History, it is a reserved name)

- we can't see what goes in goatheadings, but it doesn't look like an array

- if goatheadings is an array, is the value valid

[austenr]

How many elements are we talking about here? Sounds like this one headingstartRng and this one currentCols have differing number of elements.

[Opv]

You don't give us much do you?

It could be many things:

- there is no sheet called History (in fact you can't have a sheet called History, it is a reserved name)

- we can't see what goes in goatheadings, but it doesn't look like an array

- if goatheadings is an array, is the value valid

Sorry, it's a learning disability. What I am attempting to achieve is to create an array from the existing values in the heading row (Row 2) and then to name each cell in that array by its value. The end objective is to be able to reference a range, i.e., Range("Status"). I thought if I could create a script to automate this process I could use it in other sheets without having to manually create the range names.

1. I have a sheet named "(History)" as opposed to "History". If that is a problem, I can change that.

2. I have presumed that goatHeadings is an array, at least it is suppose to be I tested it earlier with Msgbox goatHeadings(i, 5) and it returned the correct value.

3. The script worked fine at the stage of just creating the array. Where I started running into a problem is when I tried to go a step further and assign names to the range of cells from which the array was created.

[Opv]

How many elements are we talking about here? Sounds like this one headingstartRng and this one currentCols have differing number of elements.

Taken alone, they are indeed a differing number of elements. However, the array is created by using headingStartRng twice, i.e:


Range(headingStartRng, Range(headingStartRng).end(xltoright)


I was under the impression that would select all cells to the right that contain values. If so, then the number of elements in that range would equal currentCols.

[Opv]

It just dawned on me that I can name the cells in that range from the values without having to first create an array. That should simply what I'm trying to do.

[Bob Phillips]

So what is in CurrentCols and headingStartRng, how does headingStartRng get set, and what does GetCols do?

[Bob Phillips]

Looking at it further, the problem seems to be that you create a 2d array, but try to process it as a 1d array.

[Opv]

Looking at it further, the problem seems to be that you create a 2d array, but try to process it as a 1d array.

Thanks. I think I'm going to forego the array process and just loop through the range and name the cells based on the values they contain.