Excel VBA - For Each loop is not running through each cell
<p>I am currently facing an issue in which my 'for each' loop is not moving onto subsequent cells for each cell in the range I have defined when I try to execute the script. The context around the data is below:</p>

<p>I have 3 columns of data. Column L contains employees, Column K contains managers, and column J contains VPs. Column K &amp; J containing managers and VPs are not fully populated - therefore, I would like to use a VBA script &amp; Index Match to populate all the cells and match employees to managers to VPs. </p>

<p>I have created a reference table in which I have populated all the employees to managers to directors and have named this table "Table 4". I am then using the VBA code below to try and run through each cell in column K to populate managers:</p>

<pre><code>Sub FillVPs()
Dim FillRng As Range, FillRng1 As Range, cell As Range

Set FillRng = Range("J2:J2000")
Set FillRng1 = Range("K2:K2000")

For Each cell In FillRng1
    If cell.Value = "" Then
        ActiveCell.Formula = _
    "=INDEX(Table4[[#All],[MGRS]], MATCH(L583,Table4[[#All],[EMPS]],0))"

 End If
  Next cell
End Sub
</code></pre>

<p>I feel that something is definitely wrong with the index match formula as the match cell "L583" is not moving to the next cell each time it runs through the loop; however, I am not sure how to fix it. I also do not know what else is potentially missing. The code currently executes, but it stays stuck on one cell.</p>

<p>Any help is greatly appreciated, and I will make sure to clarify if necessary. Thank you in advance.</p>