+ Reply to Thread
Results 1 to 5 of 5

dynamic array causes an error.

Hybrid View

Solidstan dynamic array causes an error. 01-15-2015, 04:10 PM
stnkynts Re: dynamic array causes an... 01-15-2015, 04:27 PM
Solidstan Re: dynamic array causes an... 01-15-2015, 04:42 PM
stnkynts Re: dynamic array causes an... 01-15-2015, 06:14 PM
Solidstan Re: dynamic array causes an... 01-15-2015, 06:18 PM
  1. #1
    Registered User
    Join Date
    02-01-2012
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    45

    dynamic array causes an error.

    I have a macro of which does what I need, however I don't always know what size array could be needed.
    I am trying to change my array to dynamic but this causes an error.

    My best knowlage to do this is to change
    Dim missingDutys(10) As Variant, x As Integer
    to
    Dim missingDutys() As Variant, x As Integer
    BUT DOING THIS FAILS... Why?
    My Code
    Sub missingDutys()
    
    Dim first_line As Integer, last_line As Integer, rotaTable As Worksheet, mon_Thurs As Worksheet
    Dim r1 As Integer, r2 As Integer, found As Boolean
    
    Dim missingDutys(10) As Variant, x As Integer
    
    Set rotaTable = Sheets("Rota Table")
    Set mon_Thurs = Sheets("Mon-Thurs Duty Times")
    
    Application.ScreenUpdating = False
    
        x = 0
        first_line = 7
        last_line = 73
        
    ' For each entry in the second worksheet, see if it's in the first.
    For r2 = 7 To 51
        found = False
        ' See if the r1-th entry on sheet 2 is in the sheet 1 list.
        For r1 = first_line To last_line
            If rotaTable.Cells(r1, 5) = mon_Thurs.Cells(r2, 1) Then
            ' We found a match.
                found = True
                Exit For
            End If
        Next r1
    
        ' See if we found it.
        If Not found Then
        
            ' Flag this cell.
            ' mon_Thurs.Cells(r2, 1).Interior.ColorIndex = 35
             
            missingDutys(x) = mon_Thurs.Cells(r2, 1)
            x = x + 1
            
            End If
    Next r2
    
        Dim y As Integer
        For y = 0 To UBound(missingDutys())
        rotaTable.Range("T25").Offset(y, 0).Value = missingDutys(y)
        Next y
    
    Erase missingDutys
    
    Application.ScreenUpdating = True
    
    
    End Sub
    Help would be really appreciated.
    Thanks.
    Last edited by Solidstan; 01-15-2015 at 04:23 PM.

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: dynamic array causes an error.

    You need to redim and preserve the existing array when you add to it.

  3. #3
    Registered User
    Join Date
    02-01-2012
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: dynamic array causes an error.

    Ok thanks, I have seen redim and preserve on google, but where ever I put it this line still fails.
    missingDutys(x) = mon_Thurs.Cells(r2, 1)
    Any help where and how I would put redim preserve?
    Thanks.

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: dynamic array causes an error.

    This should help you.

    Sub example()
    Dim missingDutys() As String
    Dim mon_Thurs As Worksheet: Set mon_Thurs = Sheets("Sheet1")
    Dim r2 As Long, i As Integer
    Dim bDim As Boolean
    
    bDim = False
    
    For r2 = 1 To 12
        If bDim = False Then
            ReDim missingDutys(0 To 0) As String
            missingDutys(0) = mon_Thurs.Cells(r2, 1)
            bDim = True
        ElseIf bDim = True Then
            ReDim Preserve missingDutys(0 To UBound(missingDutys()) + 1) As String
            missingDutys(UBound(missingDutys())) = mon_Thurs.Cells(r2, 1)
        End If
    Next r2
        
    For i = LBound(missingDutys()) To UBound(missingDutys())
        Debug.Print missingDutys(i)
    Next i
    
    End Sub

  5. #5
    Registered User
    Join Date
    02-01-2012
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: dynamic array causes an error.

    ahah, looks a lot cleaner than my code..
    Thanks, I'll give this ago tommorrow.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Loop new messages containing a table, populate a dynamic array, paste array to Excel
    By laripa in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2013, 07:20 AM
  2. [SOLVED] Populate Listbox with all rows of a dynamic array where elements of a single array match.
    By Tayque_J_Holmes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-07-2012, 04:54 AM
  3. dynamic array redim preserve: run-time error 9, Subscript out of range.
    By Broesel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2009, 08:39 AM
  4. Runtime error '9'. Subscripts out of range - Dynamic array
    By akabraha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2007, 05:38 PM
  5. Subscript out range error when redimensioning dynamic array
    By Crazy Cat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2006, 02:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1