how to refer to an element in a named range (array/matrix)

[Pascalvrolijk]

Hi all, i would like to ask your help on this. Been trying, been googling but didnt get it yet..

In Excel I named cell A1 as: selected_fund and I named cells A5:A10 as fundnames. So in excel I want to do a for loop as follows:
for i=1 to 6
[selected_fund].value=[fundnames](i).value
...
next i

This should result in A1=A5.value, then A1=A6.value, ..., then A1=A10.value.
If I replace [fundnames](i).value by [A5], [A6], ..., [A10] it works fine of course, but i want to use reference to an element in an array/matrix.

however i get an error on [fundnames](i).value: "incorrect number of arguments or incorrect property assignment"

Who can help me out to make the reference to an element in [fundnames]?

Thanks in advance,
Kind regards,
Pascal.

 

[rlv01]

The thing to remember about named ranges is that they are still ranges and must be treated as such. So the loop you describe would look something like this.

    With [fundnames]
        For i = 1 To .Rows.count
            [selected_fund].Value = .Cells(i, .Column)
        Next i
    End With

The same could also be written in other ways, depending on how you chose to refer to the named range.

    With Range("fundnames")
        For i = 1 To .Rows.count
            Range("selected_fund").Value = .Cells(i, .Column)
        Next i
    End With

    With Names("fundnames").RefersToRange
        For i = 1 To .Rows.count
            Names("selected_fund").RefersToRange.Value = .Cells(i, .Column)
        Next i
    End With

 

[Pascalvrolijk]

thank you for your reply, from your answer i deducted the following solution:
for i=1 to 6
[selected_fund].value=[fundnames].cells(i).value
...
next i