VBA, Nothing Returned when searching through array = nothing-CodePudding

I am new to VBA however i have created this but i am struggling to get an outcome from WhatToFind as it doesn't give back any error whether the data is in the cell or not.

I have tried my best to find a resolution but i couldn't, i expect it will be something simple that i am missing.

Any chance of a few pointers?

   Sub CheckColumns()
        Dim rngToSearch As Range
        Dim WhatToFind, ListOfMissing As Variant
        Dim iCtr As Long
    
        Set rngToSearch = ThisWorkbook.Worksheets("Sheets1").Range("A2:ZZ2")
    
        WhatToFind = Array("Test","Test1","Test2", "Dummy", "Dummy1", "Dummy2")
    
        With rngToSearch
            For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
                If WhatToFind(iCtr) < 1 Then
                    'MsgBox WhatToFind(iCtr) & " Header Not Found"

                    ListOfMissing = ListOfMissing & ";" & WhatToFind
                End If
            Next
        End With
    MsgBox ListOfMissing = ListOfMissing & ";" & WhatToFind
  End Sub

CodePudding user response：

Please be aware of the variable declaration - you have to declare each variable explicitly - also when they are in one row.

Furthermore it is useful to read the range to an array (arrToSearch) as this is much faster than reading each cell from a range.

Sub CheckColumns()


    Dim arrToSearch As Variant, arrWhatToFind As Variant
    arrToSearch = ThisWorkbook.Worksheets("Sheets1").Range("A2:ZZ2")
    arrWhatToFind = Array("Test", "Test1", "Test2", "Dummy", "Dummy1", "Dummy2")
 
    Dim ListOfMissing As String
    Dim iSearch As Long, iFind As Long
    Dim fFound As Boolean

    For iFind = LBound(arrWhatToFind) To UBound(arrWhatToFind)
        fFound = False
        For iSearch = 1 To UBound(arrToSearch, 2)
            If arrToSearch(1, iSearch) = arrWhatToFind(iFind) Then
                fFound = True
                Exit For
            End If
        Next
        If fFound = False Then
            ListOfMissing = ListOfMissing & ";" & arrWhatToFind(iFind)
        End If
    Next

    MsgBox ListOfMissing
End Sub

CodePudding user response：

You're not actually looking for the values you are trying to look for. I'm using .Find here.

You also cannot append an entire array to a string, you need to index it.

 Sub CheckColumns()
    Dim rngToSearch As Range
    Dim WhatToFind As Variant, ListOfMissing As Variant
    Dim fndrng As Range
    Dim iCtr As Long
    
    Set rngToSearch = ThisWorkbook.Worksheets("Sheet1").Range("A2:ZZ2")
    
    WhatToFind = Array("Test", "Test1", "Test2", "Dummy", "Dummy1", "Dummy2")
    
    With rngToSearch
        For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
            Debug.Print .Cells(1, 1).Address
            Set fndrng = .Find(WhatToFind(iCtr), .Cells(1, 1), xlValues, xlWhole) '.cells(1, 1) refers to the top left most cell of the range A2 in this case
            If fndrng Is Nothing Then
                'MsgBox WhatToFind(iCtr) & " Header Not Found"
                If ListOfMissing = "" Then 'Dodging an extra ";"
                    ListOfMissing = WhatToFind(iCtr)
                Else
                    ListOfMissing = ListOfMissing & ";" & WhatToFind(iCtr)
                End If
            End If
        Next
    End With
    MsgBox ListOfMissing 'This line was wrong in your example
End Sub

CodePudding user response：

Alternative using Match() in one go

A late post demonstrating a tricky way to verify a checklist of found positions (or error in case of non-findings) via Application.Match() - c.f. section b); note the comparison of two arrays as function arguments:

Option Explicit

Sub CheckColumnsTM()
'a) define arrays
    Dim arrToSearch As Variant
    arrToSearch = ThisWorkbook.Worksheets("Sheets1").Range("A2:ZZ2")  '  one-based 2-dim datafield
    Dim arrWhatToFind As Variant                                      ' zero-based 1-dim array
    arrWhatToFind = Array("Test", "Test1", "Test2", "Dummy", "Dummy1", "Dummy2")
'b) get check list of findings in one go
    Dim chk As Variant                                                ' one-based positions or Error 2042
    chk = Application.Match(arrWhatToFind, arrToSearch, 0)            ' << Match positions
'c) write missing search strings to checklist (starting at position 1)
    Dim i As Long, ii As Long
    For i = 1 To UBound(chk)
        If IsError(chk(i)) Then ii = ii   1: chk(ii) = arrWhatToFind(i - 1)
    Next
    ReDim Preserve chk(ii - 1)                                        ' redim to number of findings
'd) display list of missing elements in VB Editor's immediate window
    Dim ListOfMissing As String
    ListOfMissing = Join(chk, ";")
    Debug.Print ListOfMissing                                         ' ~~> Test1;Dummy1
End Sub