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?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
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
Please remember to subscribe to my youtube channel
Been helped? Get these!
