Using ArrayLists in VBA

Question

I am currently modifying a heavily moded excel schedule workbook. I have a list master list of therapists(Therapist Selector Sheet) that when a check box is checked and the magic button is pressed I want to the list to check for names in the target sheet(All Therapists) in the specified range.

If the therapist has become unchecked then the program then the program erased the necessary data for those rows and sorts everything. That part is working fine.

Next I want the program to put names that aren't already there in the next open spot. I am trying to do this by first populating an ArrayList with all the names that have a check by them.

If the program finds a duplicate I have it remove the duplicate from the array. Then when it is done removing duplicates it loops through the names and prints each one in the first cell in the column with a hypen (all empty cells are turned into hyphens).

I can't get this to work. I keep getting a

runtime error -2146233079 80131509

Does anyone see a better way to handle this? Or at least see where I am going wrong?

The first sub calls the second sub to clear and sort:

Private Sub AddDailyTherapists(PasteToRange As range, TrueFalseRange As range, StartCell As range, SortRange As range)
    Call ClearUnselectedTherapists(PasteToRange, TrueFalseRange, StartCell, SortRange)
    Dim Names As Object
    Set Names = CreateObject("System.Collections.ArrayList")
    For Each cel In TrueFalseRange
        If cel.value = True Then
            Names.Add cel.Parent.Cells(cel.Row, 4).value
        End If
    Next cel
    For Each n In PasteToRange
        For Each nm In Names
            If nm = n.value Then
                Names.Remove nm
            End If
        Next nm
    Next n
    StartCell.Activate
    For Each nm In Names
        Do While (ActiveCell.value <> "-")
            ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
        Loop
        ActiveCell.value = nm
    Next nm
End Sub

Public Sub ClearUnselectedTherapists(PasteToRange As range, TrueFalseRange As range, StartCell As range, SortRange As range)
    Sheets("All Therapists").Activate
    StartCell.Activate
    For Each cell In TrueFalseRange
        If cell.value = False Then
            Name = cell.Parent.Cells(cell.Row, 4).value
            For Each cel In PasteToRange
                If Name = cel.value Then
                    cel.value = "-"
                    cel.Offset(0, 1).range("A1:R1").Select
                    Selection.ClearContents
                    Exit For
                End If
            Next cel
        End If
    Next cell
    With ActiveWorkbook.Worksheets("All Therapists").Sort
        .SetRange SortRange
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

The modified spreadsheet:

spreadsheet that is modified

The sheet with the checkboxes:

Spreadhsheet with checkboxes

I ended up being able to do it with an Array instead of the array list — benjaminhmlucas, Nov 12 '17 at 23:54
https://stackoverflow.com/questions/19252285/removing-items-in-a-list-while-iterating-through-it-with-for-each-loop — Tim Williams, Nov 12 '17 at 23:54

score 0 · Answer 1 · answered Nov 12 '17 at 23:55

Private Sub AddDailyTherapists(PasteToRange As range, TrueFalseRange As range, StartCell As range, SortRange As range)
'
Call ClearUnselectedTherapists(PasteToRange, TrueFalseRange, StartCell, SortRange)

Dim Names(0 To 11) As String
i = 0
For Each cel In TrueFalseRange
    If cel.value = True Then
        Names(i) = cel.Parent.Cells(cel.Row, 4).value
        i = i + 1
    End If
Next cel
For Each n In PasteToRange
    For j = 0 To UBound(Names)
        If Names(j) = n.value Then
            Names(j) = ""
        End If
    Next j
Next n
StartCell.Activate
For k = 0 To UBound(Names)
    Do While (ActiveCell.value <> "-")
        ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    Loop
    If Names(k) <> "" Then
        ActiveCell.value = Names(k)
    End If
Next k

End Sub

Using ArrayLists in VBA

1 Answers1