Missing Value Checker not working

[iliauk]

Hi All,

I attempted to write something that would print out a list of values that are in an array but not in Column A, however haven't had much success. Any pointers would be greatly appreciated, thanks.

Sub Listchecker()

Dim LastRow As Long
Dim Countrylist As Variant
Dim Missinglist As String
Dim Countryno As Long


Countryno = WorksheetFunction.Count(Countrylist)


Missinglist = ""


With ActiveSheet
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With


Countrylist = Array("Austria", "Belgium", "Bulgaria", "Czech Republic", "Denmark", "Finland", "France", "Germany", "Hungary", "Ireland", "Italy", _
"Lithuania", "Netherlands", "Poland", "Romania", "Slovakia", "Sweden", "UK", "Croatia", "Estonia", "Greece", _
"Latvia", "Norway", "Portugal", "Slovenia", "Spain")


With Range("A1:A" & LastRow)


    For i = 1 To LastRow


        For j = 1 To Countryno
            If Countrylist(j) = Range("A" & i) Then
            Else
            Missingcont = Countrylist(j)
            End If
        Next j
    
        Missinglist = Missinglist & " " & Missingcont
    
    Next i


End With


'Print list of missing
Range("B1").Value = Missinglist


End Sub

Listing Austria, Belgium, Spain in Col A; it prints out 'Belgium 3 times.

 

[iliauk]

I have something that works now (at least in the locals window - I get the correct string), however it has an error at the very end (subscript out of range)

EDIT: AH I was starting from 0, so now to countryno - 1 as upper bound works!

Sub Listchecker()
*Compares values in Columns A against Array = Countrylist & returns what not present in Column


Dim LastRow As Long
Dim Countrylist As Variant
Dim Missinglist As String
Dim Countryno As Long


Missinglist = ""


With ActiveSheet
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With


***CHANGE AS NEEDED***
Countrylist = Array("Austria", "Belgium", "Bulgaria", "Czech Republic", "Denmark", "Finland", "France", "Germany", "Hungary", "Ireland", "Italy", _
"Lithuania", "Netherlands", "Poland", "Romania", "Slovakia", "Sweden", "UK", "Croatia", "Estonia", "Greece", _
"Latvia", "Norway", "Portugal", "Slovenia", "Spain")
***CHANGE AS NEEDED***


Countryno = Application.CountA(Countrylist)


***CHANGE AS NEEDED***
With Range("A1:A" & LastRow)
***CHANGE AS NEEDED***


For j = 0 To (Countryno - 1)


For i = 1 To LastRow
                          
            Missingcont = Countrylist(j)
         
            If Countrylist(j) = Range("A" & i) Then
            Missingcont = ""
            If Missingcont = "" Then Exit For
            
            End If
        
            Next
      
            If Missinglist = "" Then
            Missinglist = Missingcont
            Else
            Missinglist = Missinglist & ", " & Missingcont
            End If
            
        Next
       
End With


'Print list of missing
***CHANGE AS NEEDED***
Range("B1").Value = Missinglist
***CHANGE AS NEEDED***


End Sub