CODEWITHSUNDEEP
excelvba
user2137354
user2137354

Reputation:

Excel VBA populate array from named range

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

Upvotes: 6

Views: 75774

Answers (2)

Automate This
Automate This

Reputation: 31364

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")

Upvotes: 7

Sathish Kothandam
Sathish Kothandam

Reputation: 1520

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

Upvotes: 4

Related Questions