none
varaint Table RRS feed

  • Question

  • Hi

    I have a stament in my code as follows which creates a 1 dimensionale array.

    ReDim varTable(1 To 22)

    Later in the code I do:

    Redim Presever varTable (1 to noofentries)

    and then as part of the close workbook  I write this to a sheet in one column as follows:

    sheet1.range("A3").resize(rowsize:=noofentries,columnsize:=1) = varTable

    So far all is well.

    Later on when i reopen the workbook as part of the open process I want to retrieve the data from sheet1 and place in the array.I try :

    vartable = sheet1.range("A3").currentregion.resize(rowsize:=noofentries,columnsize:=1)

    and I keep getting a 2 dimensional array i.e. vartable (1 to 3 , 1 to 1) which then needs 2 indices to access.

    I need it to be one dimension otherwise the search code (not incdluded here) will not work.

    what i am doing wrong?

    thanks

    Peter

    Monday, October 9, 2017 10:46 AM

Answers

  • The Value of a multi-cell range is always a two-dimensional range even if the range consists of only one column (or only one row).

    You could fill varTable in a loop:

        Dim r As Long
        For r = 1 To noofentries
            varTable(r) = Sheet1.Range("A" & r - 1).Value
        Next r


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Monday, October 9, 2017 3:47 PM
    • Marked as answer by py1 Monday, October 9, 2017 4:45 PM
    Monday, October 9, 2017 3:47 PM

All replies

  • The Value of a multi-cell range is always a two-dimensional range even if the range consists of only one column (or only one row).

    You could fill varTable in a loop:

        Dim r As Long
        For r = 1 To noofentries
            varTable(r) = Sheet1.Range("A" & r - 1).Value
        Next r


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Monday, October 9, 2017 3:47 PM
    • Marked as answer by py1 Monday, October 9, 2017 4:45 PM
    Monday, October 9, 2017 3:47 PM
  • thanks Hans - i ended up doing something like this.

    Out of interest , why have you got r-1 in the range?

    Will this not create a possibe address of A0?

    thanks

    Peter

    Monday, October 9, 2017 4:45 PM
  • Sorry about that, I changed something in the code at the last moment and forgot to adjust the r - 1.

    If your range begins in A1, use Range("A" & r)

    If the range begins in A2, use Range("A" & r + 1)

    If the range begins in A3, use Range("A" & r + 2)

    Etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 9, 2017 9:08 PM
  • thanks Hans - no problem
    Tuesday, October 10, 2017 8:03 AM