PDA

View Full Version : Solved: dynamic named array from multiple columns



snidera
09-08-2006, 01:21 PM
I've searched all over and can't find what i need. I hope somebody here can help.

I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.

I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information

What i've tried::banghead:
Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code).
The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.

modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
ReDim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Lists!Cells(2,i+7),0,0,COUNTA(*****),1)", Visible:=True
Next i

Basically, I dont know how to get:
COUNTA(cells(2,i+7):Cells(last row,i+7))
as a range that excel understands

Any ideas? Thanks in advance

snidera
09-08-2006, 02:33 PM
Next Try:

modelcount = Range("G7")
For i = 1 To modelcount
ReDim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
Range1 = Cells(2, i + 7).Address(xlA1)
lastRow = Cells(rows.Count, i + 7).End(xlUp).Row
Range2 = Cells(lastRow, i + 7).Address(xlA1)
Reference = Cells(2, i + 7).Address(xlA1)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True
Next i


This gets me the range i need, but doesnt create the dynamic named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate the the specific cells. It shows the variable names rather than the cell range the variable represents.

makako
09-08-2006, 04:19 PM
can you give an example of your workbook?

snidera
09-10-2006, 08:42 PM
I had to take out a lot of proprietary info, and i did it in a hurry. The code doesnt run on my laptop for some reason (it should at least give me an error).

Dave
09-10-2006, 10:38 PM
Shouldn't this: For i = 1 To modelcount
be this: For i = 0 To modelcount
If "i" can never be 0 then you never fill the array position Models(i) unless you're using option base 1... which the rest of your code doesn't support. Could be worth a trial. Dave
re-edit: whoops I see that you're also using "i" to reference cells and of course there is no 0 cell location which may be a problem. Use option base 1 and this instead. HTH. ReDim Preserve Models(1 To i)

snidera
09-11-2006, 06:11 AM
Shouldn't this: For i = 1 To modelcount be this: For i = 0 To modelcount If "i" can never be 0 then you never fill the array position Models(i) unless you're using option base 1... which the rest of your code doesn't support. Could be worth a trial. Dave
re-edit: whoops I see that you're also using "i" to reference cells and of course there is no 0 cell location which may be a problem. Use option base 1 and this instead. HTH. ReDim Preserve Models(1 To i)

You are correct, thats another error, but it was functioning, all except for placing Range address in the formula rather than the variable name. This is what happens when you try to do coding after taking a VB course in college 4 years ago.....

I recieved this code on another forum. It works great, too bad i don't understand it (and my laptop doesn't have all the help files apparently). I'll have to wait till I get back in the office on Thur to try and figure it out.

For Each rCol In Range("H1", Range("IV1").End(xlToLeft))
Range(Cells(2, rCol.Column), Cells(rows.Count, rCol.Column).End(xlUp)).Name = rCol.Text
Next rCol