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.
Bookmarks