Check if value exists in a range (simple TRUE/FALSE needed)

[bukimi]

Hello!

I have a database, which consists of thousands of rows (10-50k rows) and I need to alter value in one column if 3 different conditions are met for that row at the same time (linked by "AND").
For first two I have it easy, because it simply checks if value is "Modem" or not and second one is similar.

Third column is a number, like "14525" and I have a separate file (let's call it List.xls), where one column is a short list of possible (correct) numbers.
I need to check if number in my database is one of numbers on the list, so it can be used in my "If" code.

Currently it looks more or less like this:

Dim lastrow As Long
Dim sht As Worksheet


Set sht = ActiveWorkbook.ActiveSheet
lastrow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row


With Sheets("Arkusz1")
    For i = 2 To lastrow
        If .Cells(i, 1) = "Equipment" And .Cells(i, 22) = "Modem" And .cells(i,21)'/IS A VALID NUMBER (from a seperate list) Then
        .Cells(i, 1) = "Other stuff"
    Next i
End With

VLOOKUP seems not suitable here, because it returns a value, not True/False.

By the way, is there a faster way to loop that condition over all these rows than simple for-next loop?

Thank you in advance for all help!

 

[JLGWhiz]

set your list range to a variable and use a worksheet function to check if the cell value matches a list item.
Example:

Dim rng As Range
Set rng = Sheets(2).Range("myList") 
If .Cells(i, 1) = "Equipment" And .Cells(i, 22) = "Modem" And Appliction.CountIf(rng, .cells(i,21).Value) > 0 Then

 

[bukimi]

It works! Thank you!

 

[JLGWhiz]

It works! Thank you!

You're welcome,
regards, JLG