Printable View
Could someone please tell me why I am getting an Error 9 (Subscript out of range) with the following loop?
[VBA]
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
[/VBA]
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
How many elements are we talking about here? Sounds like this one headingstartRng and this one currentCols have differing number of elements.
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.Quote:
Originally Posted by xld
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.
Taken alone, they are indeed a differing number of elements. However, the array is created by using headingStartRng twice, i.e:Quote:
Originally Posted by austenr
[VBA]
Range(headingStartRng, Range(headingStartRng).end(xltoright)
[/VBA]
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.
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.
So what is in CurrentCols and headingStartRng, how does headingStartRng get set, and what does GetCols do?
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.Quote:
Originally Posted by xld