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