Results 1 to 9 of 9

Thread: Reading a range into a variant array.

  1. #1
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    55
    Articles
    0
    Excel Version
    2019

    Reading a range into a variant array.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 'this command works
    myarr = Range("Words").Value 'this command does not work
    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 AliGW; 2019-05-03 at 05:47 AM. Reason: Code tags added, as required by forum guidelines.

  2. #2
    Seeker gue's Avatar
    Join Date
    Nov 2018
    Posts
    19
    Articles
    0
    Excel Version
    Office Professional Plus 2016

    Smile

    You wrote
    I am trying to read an entire table into myarr
    but with
    Code:
    myarr = Range("Words").Value
    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

    Attached Files Attached Files

  3. #3
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    55
    Articles
    0
    Excel Version
    2019
    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

  4. #4
    Acolyte Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    74
    Articles
    0
    Excel Version
    2013
    How large is your table?
    Also do you have any cells that contain errors?

  5. #5
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    55
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by Fluff View Post
    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

  6. #6
    Acolyte Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    74
    Articles
    0
    Excel Version
    2013
    I don't think it's reading the table into the array, try
    Code:
    myarr = ThisWorkbook.ActiveSheet.ListObjects("Words").Range

  7. #7
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    55
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by Fluff View Post
    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.

  8. #8
    Acolyte Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    74
    Articles
    0
    Excel Version
    2013
    You're welcome & thanks for the feedback

  9. #9
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •