Excel Fill range with missing values of an array [

Question

So i am looking to write a macro that would basically select a range, compare the values in the cells with values from say an array and add the missing ones to the end of the range.

Answer 1

Try this example using strings:

To get you started, you could use Cells(row#, column#), instead of Range("A1") and perform loops over the array and loop through the cells by row, comparing each value. Then adding the values that don't match to a new array, that you will later pass back into the sheet in another loop.

Assuming your values start at Sheets("Sheet1").Range("A1"). With the following example names: Sam, Jim, Stanly, Jeff, Mike, Jeff, Toby.

Dim lastRow As Integer
Dim i As Integer
Dim r As Integer
Dim n As Integer
Dim unMatchedArray() As String

Private Sub ArrayChecker()

    Dim myArray(7) As String
    Dim match As Boolean

    n = 0

    myArray(0) = "Jeff"
    myArray(1) = "Stanly"
    myArray(2) = "Mike"
    myArray(3) = "Sam"
    myArray(4) = "Toby"
    myArray(5) = "Reginald"
    myArray(6) = "Wolfgang"
    myArray(7) = "Manual"

    Call GetLastRow

    For i = 0 To UBound(myArray)

        r = 1
        match = False
        For r = 1 To lastRow
            If myArray(i) = Sheets("Sheet1").Cells(r, 1) Then
                match = True
                Exit For
            End If
        Next r

        If match = False Then
            ReDim Preserve unMatchedArray(n)
            unMatchedArray(n) = myArray(i)
            n = n + 1
        End If

    Next i

    n = n - 1

    If n > 0 Then
        Call AddToSheet
    End If

End Sub

Private Sub GetLastRow()

    ' checking Range("A1:A65536")
    lastRow = Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row

End Sub

Private Sub AddToSheet()

    Call GetLastRow
    r = lastRow + 1
    i = 0

    For i = 0 To n
        Sheets("Sheet1").Cells(r, 1) = unMatchedArray(i)
        r = r + 1
    Next i

End Sub