“Can you let me know how to check if a particular value is present in a named list. Or in a different worksheet”<\/p><\/blockquote>\n
You can use the “countif<\/strong>” function.<\/p>\n ex. =countif({value to look for}, {range to look in})<\/em><\/p>\n if the result is greater than zero, the value is in the range, if zero then it’s not there.<\/strong><\/p>\n Take a look at the above example. <\/p>\n 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.<\/p>\n * Here I'm using the \"countif\" function, and returning the value to a variable called \"intct\" :<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/11\/countif.png?resize=688%2C333\")
<\/p>\nNow how do you use the countif function with VBA?<\/h2>\n
\r\n\r\n\r\n\r\nFunction GetNextInvoiceID(strYear, strMonth) As String\r\n Dim intCounter As Integer\r\n Dim varPos As Variant\r\n Dim lngLastRow As Long\r\n Dim strCounter As String\r\n Dim blnFound As Boolean\r\n \r\n \r\n lngLastRow = FindLastRow(\"A\")\r\n \r\n \r\n For intCounter = 1 To 100\r\n If Len(intCounter) < 10 Then\r\n strCounter = \"0\" & intCounter\r\n Else\r\n strCounter = intCounter\r\n End If\r\n strInvoiceID = strYear & strMonth & strCounter\r\n \r\n blnFound = False\r\n \r\n intct = Application.WorksheetFunction.CountIf(Range(\"F1:F\" & lngLastRow), strInvoiceID)\r\n If intct > 0 Then\r\n\r\n Else\r\n\r\n GetNextInvoiceID = strInvoiceID\r\n Exit For\r\n End If\r\n \r\n \r\n Next\r\n\r\nEnd Function\r\n\r\nFunction FindLastRow(WhichColumn As String) As Long\r\n \r\n 'FINDS THE LAST ROW BASED ON THE COLUMN LETTER <---\r\n \r\n Dim lngLastRow As Long\r\n \r\n 'move to the last row on the worksheet and find the last used cell.\r\n \r\n With ActiveSheet\r\n lngLastRow = ActiveSheet.Cells(1048576, WhichColumn).End(xlUp).Row\r\n End With\r\n\r\n FindLastRow = lngLastRow\r\n\r\nEnd Function\r\n<\/pre>\n