Looping through a range..
 

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

--
Helping Is always a good thing

Looping through a range..
 

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

"QuietMan" skrev i meddelelsen
...
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

--
Helping Is always a good thing