Reading a range into a variant array.

Heyjoe

New member
Joined
Jan 3, 2019
Messages
59
Reaction score
0
Points
0
Location
USA
Excel Version(s)
2019
Hello programmers,

I am trying to read a range into a variant array.

Code:
Private Sub Readwords_Click()
Dim myarr As Variant
myarr = Range("a7:b8").Value [COLOR=#008080]'this command works
[/COLOR]myarr = Range("Words").Value [COLOR=#006400]'this command does not work[/COLOR]
End Sub
In the first case I am able to read a range into the array myarr. This range is not part of a table.

In the second case (right before End Sub) I am trying to read an entire table into myarr but I get a 1004 error. I suspect that I may have improper syntax in the second case.

Why does the last command not work?
 
Last edited by a moderator:
You wrote
I am trying to read an entire table into myarr
but with
Code:
[COLOR=#3E3E3E]myarr = Range("Words").Value[/COLOR]
you reference a named range and not a table. With a named range it would work

A complete table you can access with
Code:
myarr=ThisWorkbook.ActiveSheet.ListObjects("Words")


I attached a file with copying a range, a named range and a table as example

 

Attachments

  • Book1.xlsm
    18.8 KB · Views: 33
OK, it seems to read the table into the array. However, if I want to display an element of the array using msgbox it gives me error 13 type mismatch.
Code:
Private Sub Readwords_Click()
Dim myarr As Variant
Sheets("words").Select
myarr = ThisWorkbook.ActiveSheet.ListObjects("Words")
MsgBox myarr(4, 4)
End Sub

End Sub
 
How large is your table?
Also do you have any cells that contain errors?
 
How large is your table?
Also do you have any cells that contain errors?

I used the filter to determine that there are no errors. However there are blank cells.

There are over 350 rows and it goes to Column G
 
I don't think it's reading the table into the array, try
Code:
myarr = ThisWorkbook.ActiveSheet.ListObjects("Words").Range
 
I don't think it's reading the table into the array, try
Code:
myarr = ThisWorkbook.ActiveSheet.ListObjects("Words").Range

Adding .Range to the command did the trick. Thank you both for your help.
 
You're welcome & thanks for the feedback
 
The simplest method:

Code:
Sub M_snb()
    sn = [Table1]
    MsgBox UBound(sn) & vbTab & UBound(sn, 2)
End Sub

In your case:

Code:
Sub M_snb()
  sn = [Words]
End Sub
 
Back
Top