Here’s an example of how to check if a value exists in a range of cells:

It is actually a response from a question I received:

“Can you let me know how to check if a particular value is present in a named list. Or in a different worksheet”

You can use the “countif” function.

ex. =countif({value to look for}, {range to look in})

if the result is greater than zero, the value is in the range, if zero then it’s not there.

Take a look at the above example.

You’ll see that the built in countif Excel function returns a value greater than zero if the value be sought after is in the list (range), and zero if it’s not.

Now how do you use the countif function with VBA?

Function GetNextInvoiceID(strYear, strMonth) As String
    Dim intCounter As Integer
    Dim varPos As Variant
    Dim lngLastRow As Long
    Dim strCounter As String
    Dim blnFound As Boolean
    
   
    lngLastRow = FindLastRow("A")
    
    
    For intCounter = 1 To 100
        If Len(intCounter) < 10 Then
            strCounter = "0" & intCounter
        Else
            strCounter = intCounter
        End If
        strInvoiceID = strYear & strMonth & strCounter
        
        blnFound = False
        
        intct = Application.WorksheetFunction.CountIf(Range("F1:F" & lngLastRow), strInvoiceID)
        If intct > 0 Then

        Else

           GetNextInvoiceID = strInvoiceID
           Exit For
        End If
        
    
    Next

End Function

Function FindLastRow(WhichColumn As String) As Long
    
    'FINDS THE LAST ROW BASED ON THE COLUMN LETTER <---
    
    Dim lngLastRow As Long
    
    'move to the last row on the worksheet and find the last used cell.
    
    With ActiveSheet
        lngLastRow = ActiveSheet.Cells(1048576, WhichColumn).End(xlUp).Row
    End With

    FindLastRow = lngLastRow

End Function

* Here I'm using the "countif" function, and returning the value to a variable called "intct" :

intct = Application.WorksheetFunction.CountIf(Range("F1:F" & lngLastRow), strInvoiceID)

Questions? Contact Me