Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Redim (out of subscript error) (Excel 2K (VBA))

    This code essentially is supposed to get a recordset of data associated with a certain occurrence on column "D" pretty much as a database would do it. If data on clumn "D" equals a certain value, the data on cells G, H and so on the same row are to be input into a listbox control. I keep getting an "Subscript out of range" error when I am ReDIm'ing the array (NOT while assigning data to an arrray member). Can someone spot wht is wrong with this code?. Thanks in advance.

    I have declared the variable vector (which is an array) vector as follows:

    dim vector() as variant
    dim lastRow as integer

    'get the # of the last column
    lastRow = Worksheets("Results").UsedRange.Rows.Count

    'bundles the cells on column D with legit data
    For Each criteria In Worksheets("Results").Range("d2:d" & lastRow).Cells

    If criteria.Value = frmMain.cbPart.Value Then

    ReDim Preserve vector(index_y, 9) '<~~~~~~~~~~~ ERROR RIGHT HERE

    'row of the current cell whose value equals the part number being searched
    rrrIndex = criteria.Row


    vector(index_y, 0) = aw.Range("g" & rrrIndex).Value
    vector(index_y, 1) = aw.Range("h" & rrrIndex).Value
    vector(index_y, 2) = aw.Range("i" & rrrIndex).Value
    vector(index_y, 3) = aw.Range("j" & rrrIndex).Value
    vector(index_y, 4) = aw.Range("k" & rrrIndex).Value
    vector(index_y, 5) = aw.Range("l" & rrrIndex).Value
    vector(index_y, 6) = aw.Range("m" & rrrIndex).Value
    vector(index_y, 7) = aw.Range("n" & rrrIndex).Value
    vector(index_y, 8) = aw.Range("o" & rrrIndex).Value
    vector(index_y, 9) = aw.Range("p" & rrrIndex).Value
    index_y = index_y + 1
    End If

    Next criteria

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Redim (out of subscript error) (Excel 2K (VBA))

    With ReDim Preserve you can only change the last dimension. So you'll have to switch the dimensions and use

    ReDim Preserve vector(9, index_y)

    Of course, you'll have to modify the rest of the code to suit this change.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Redim (out of subscript error) (Excel 2K (VBA)

    Wow.....thanks for the prompt tip

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Redim (out of subscript error) (Excel 2K (VBA))

    The following is from XL Help:

    If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

    You are trying to change the first dimension.
    Legare Coleman

Posting Permissions

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