I need to populate an array's values with cells from a named range in another workbook. So far what I have is not working for me:
Dim vArray() as Variant
vArray = Workbooks("Book2").Worksheets("Sheet1").Range("myRange")
Debug.Print vArray(1) 'yields error
Also no luck with:
vArray = Workbooks("Book2").Names("myRange")
or
vArray = Workbooks("Book2").Names("myRange").RefersToRange
Try changing the print line to this:
Debug.Print vArray(1, 1)
Here is how you can loop through them:
Sub Test()
Dim vArray() As Variant
vArray = Range("myRange")
Dim i As Long
For i = LBound(vArray, 1) To UBound(vArray, 1)
Debug.Print vArray(i, 1)
Next
End Sub
*Edit*
To use 'Book2' without having to activate it you can do this:
Sub Test()
Dim vArray() As Variant
Dim rng As Range
Dim wbk As Workbook
Set wbk = Excel.Application.Workbooks("Book2.xls")
Set rng = wbk.Worksheets("Sheet1").Range("myRange")
vArray = rng
Dim i As Long
For i = LBound(vArray, 1) To UBound(vArray, 1)
Debug.Print vArray(i, 1)
Next
End Sub
To open book2 from another book change line 5 to this:
Set wbk = Excel.Application.Workbooks.Open("C:\Users\myname\Desktop\Book2.xls")
One more method.. TESTED
Sub Test()
Dim vArray As Range
Dim rng As Range
With ActiveSheet
Set vArray = .Range("myRange")
For Each rng In vArray
Debug.Print rng.Value
Next rng
End With
End Sub
