Consulting

Results 1 to 4 of 4

Thread: Dynamic Array not Resizing

  1. #1
    VBAX Regular
    Joined
    Feb 2018
    Location
    Portland
    Posts
    38
    Location

    Dynamic Array not Resizing

    I have the following code:
    Sub DynamicArray()
        Dim numbers() As Integer, size As Integer, i As Integer
        size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
        ReDim numbers(size)
        For i = 1 To size
            numbers(i) = Cells(i, 1).Value
        Next i
          MsgBox numbers(size)
    End Sub
    My worksheet looks like this and when I run my code, I keep getting "10" when the last value in my array is "20". Any ideas? ThanksArrayError.jpg

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    This contains an unqulified reference (in red):
    numbers(i) = Cells(i, 1).Value
    If the active sheet is not the same sheet as Worksheets(1) then you're likely to get incorrect results (this assumes that the code is sitting in a standard code module and not a sheet's code module - if it's in a sheet's code module then an unqulaified reference refers to that sheet's cells, so make sure the code is in an apppropriate module)
    So in the first instance, you could try changing just that line to:
    numbers(i) = Worksheets(1).Cells(i, 1).Value

    With so many possibilities where it may go wrong, much better to attach a sample file.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Well, FWIW ... I get 8 as an answer, but P45cal could have the issue

    I keep getting "10" when the last value in my array is "20". Any ideas? Thanks
    I don't see any 20 in the data


    Capture.JPG



    Option Explicit
    
    
    Sub DynamicArray()
        Dim numbers() As Integer, size As Integer, i As Integer
        
        size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
        
        MsgBox size
        
        ReDim numbers(size)
        
        MsgBox LBound(numbers) & " -- " & UBound(numbers)
        
        For i = 1 To size
            numbers(i) = Cells(i, 1).Value
        Next i
          
        MsgBox numbers(size)
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Feb 2018
    Location
    Portland
    Posts
    38
    Location
    P45Cal, your suggestion was spot on! I had my array on sheet2. But as you said, my code only worked when Sheet2 was the active sheet. By qualifying the reference to
    For i = 1 To size
            numbers(i) = Worksheets(2).Cells(i, 1).Value
        Next i
    The code ran just fine on the other sheets. Also thanks to Paul as I learned some new methods to use with arrays.

Posting Permissions

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