Looping through a range..

[QuietMan]

Does anyone know a more efficient way to write this code...as I will be
checking 50k+ rows

Sub Tester()
X = 2
Do While X < 9
Dept_Checker = Cells(X, 1)
CoversDept = Range("CoversDept")
For Each Dept In CoversDept
If Dept_Checker = Dept Then Active_Department = Dept_Checker
Next
Cells(X, 17) = Active_Department
X = X + 1
Loop
End Sub

 

[Per Jessen]

Hello

I would use For..Next rather than Do While...Loop, and exit the inner loop
if a match is found:

Sub Tester()
For r = 2 To 8
Dept_Checker = Cells(r, 1)
For Each Dept In Range("CoversDept")
If Dept_Checker = Dept Then Active_Department = Dept_Checker
Exit For
Next
Cells(r, 17) = Active_Department
Next
End Sub

Regards,
Per