vba - Macro producing incorrect results when run, but when stepping into results are correct
<p>I have a macro that inserts a <code>VLOOKUP</code> into a column. The macro has to take a number stored as text and convert it to a number, before looking up that number in another sheet.</p>

<p>The macro always produces the same results, such as reaching row 43 before starting to produce erroneous results however when using <code>F8</code> to step through the code, these incorrect results are not produced. </p>

<p>The erroneous results are that the value placed into col 13 is not equal to the number stored as text. Mostly it seems as though values from rows above and below, sometimes 2 rows below are being inserted to col 13. Almost seems to me as if 2 different threads are running at 2 different speeds or something?</p>

<p>If anyone could have a look at the loop causing the errors I would be grateful, thanks.</p>

<pre><code>For counter = 2 To NumRowsList
  checker = CInt(Sheets("Sheet2").Cells(counter, 3)    
  Sheets("Sheet2").Cells(counter, 13).Value = checker

  'Call WaitFor(0.5)
  If checker &lt; 4000 Then
    Sheets("Sheet2").Cells(counter, 14) = "=VLOOKUP(M" &amp; counter &amp; ",Sheet4!E2:F126,2,FALSE)"
  Else
    Sheets("Sheet2").Cells(counter, 14) = "=VLOOKUP(M" &amp; counter &amp; ",Sheet5!B2:C200,2,FALSE)"
  End If        
Next counter
</code></pre>

<p>I have tried a few similar variations of this code, such as using the value stored in col 13 directly rather than using the cell reference in the <code>VLOOKUP</code>, always producing the same results.</p>

<p>I even used the waitfor function to try and create a delay hoping it may synchronise the operations, but it did not help and using a delay of more than 0.5 would cause the run time of the macro to be too big.</p>

<p>UPDATE:
I did not find a perfect solution, only a long hand work around. I simply combined the Vlookups onto a single sheet, and converted the numbers stored as text to numbers outside of the vba routine. This took the error away from the number calculation (just col C * 1), and then the vlookups were looking up the correct values. Thank you for the help, regardless. </p>