How to call named ranges from an array in a for loop in Excel VBA?

Question

I created an array with named ranges in it.

Here's my code:

Sub Macro()
    ' - - - - - - - - - - - - - - - - -
    Dim fruits As Variant
    fruits = Array("Apple", "Banana", "Coconut")

    For i = 1 To Length(fruits)

     Cells(5, i).Select
        ActiveCell.FormulaR1C1 = Range(fruits.Cells(i))(1)

    Next i
    End Sub

I'd like to call these named ranges from an array in a for loop. How can I do that? Thank you in advance.

I'd like to display the values of the named ranges, let's say, in the 5th row.

user4039065 · Accepted Answer · 2018-05-03 08:20:50Z

1

1-D arrays are zero-based by default, not one-based.

Sub Macro()
    Dim fruits As Variant

    fruits = Array("Apple", "Banana", "Coconut")

    For i = lbound(fruits) to ubound(fruits)
        Cells(5, i + 1) = Range(fruits(i))(1)
        'maybe this
        'Cells(5, i + 1) = Range(fruits(i)).cells(1, 2)
    Next i
End Sub

answered May 3, 2018 at 8:20

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Übel Yildmar Over a year ago

You're super, @Jeeped! Thank you :)

Vityata · Accepted Answer · 2018-05-03 08:53:05Z

1

Array can be parsed to a range with one line:

Sub TestMe()

    Dim fruits As Variant
    Cells.Clear
    fruits = Array("Apple", "Banana", "Coconut")

    Range("A1:C1").Value2 = fruits
    Range("A5:A7").Value2 = Application.Transpose(fruits)

End Sub

Or even "fancier", with non hard-coded ranges:

Sub TestMe()

    Dim fruits As Variant
    Cells.Clear
    fruits = Array("Apple", "Banana", "Coconut")

    Dim fc As Range
    Set fc = Range("E5")

    Range(fc, fc.Offset(ColumnOffset:=UBound(fruits))) = fruits
    Range(fc, fc.Offset(UBound(fruits))) = Application.Transpose(fruits)

End Sub

edited May 3, 2018 at 8:53

answered May 3, 2018 at 8:41

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

1 Comment

Vityata Over a year ago

@Zac - w/o fancy stuff there is no fun.

Collectives™ on Stack Overflow

How to call named ranges from an array in a for loop in Excel VBA?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related