title: #VALUE Error in VBA after a specific date value
body: <p>I have written some VBA functions (listed in code below)
  I am comparing records from two worksheets using functions to return the related values from one sheet to the other.</p>
  
  <p>The first function, upon which all other functions depend on, returns the Patient ID number. </p>
  
  <p><strong>Criteria to select a Patient ID:</strong> </p>
  
  <blockquote>
    <p>The function compares date and time of patient arrival within a 30
    minute interval (since the information recieved from one source
    usually varies by a few minutes from the other), gender, clinic ID,
    and birthyear. Patient ID numbers start at around 50000, and go on
    until around 150000. I need to compare date <em>and</em> time, because from
    time to time two patients with the same gender, birthdate and clinic
    arrived on the same day.</p>
  </blockquote>
  
  <p><strong>The function fails after 100000's rows
  Beyond this only #VALUE! errors are returned.</strong> </p>
  
  <p><strong>Following is a complex scenario I tested, and found the Date and Time to be at fault.</strong></p>
  
  <blockquote>
    <ul>
    <li>Comparing only Date, with no interval, returns a normal value. </li>
    <li>The last Patient ID to work is 98472 (not all Patient IDs have been reported yet), the Patient has an arrival date of May 1st, 2018 at
    8:42pm. </li>
    <li>The next Patient ID is 100471, arriving on the 4th of May, 2018 at 10:43am. * The function returns this Patient as a #VALUE! error,
    although all parameters are there.</li>
    </ul>
  </blockquote>
  
  <p>Here is the code (pardon any rookie mistakes, I'm no professional coder):</p>
  
  <pre><code>Function EINSATZ(aufnahmdat As Date, geburtsdat As Integer, geschlecht As Integer, klinik As Integer)
      'DEFINING PARAMETERS
      'rsu_r is the regional stroke unit row
      'rsu_c is the regional stroke unit column
      'size is the patient list size
      'iffunction allows the function to work through the patient list
      'converter converts letter to integer for sex
  
      Dim rsu_r As Integer
      Dim rsu_c As Integer
      Dim size As Variant
      Dim iffunction As Single
      Dim converter As Integer
  
      'here starts the dimension definition for rsu cells
      rsu_r = ActiveCell.Row
      rsu_c = ActiveCell.Column
  
  
      'here starts the size function
      'size is predetermined to measure and print the highest value within the first 9996 cells
      For iffunction = 4 To 9999
          If Application.WorksheetFunction.IsNumber(Worksheets("Präklinik").Cells(iffunction, 5)) Then
              size = size + 1
          End If
      Next iffunction
  
      'here starts the if function
      For iffunction = 4 To size        
          If Worksheets("Präklinik").Cells(iffunction, 6).Value = "m" Then
              converter = 2
          Else
              converter = 1
          End If
  
          If Worksheets("Präklinik").Cells(iffunction, 4).Value + Worksheets("Präklinik").Cells(iffunction, 17).Value &lt; aufnahmdat + 1 / 48 _
             And Worksheets("Präklinik").Cells(iffunction, 4).Value + Worksheets("Präklinik").Cells(iffunction, 17).Value &gt; aufnahmdat - 1 / 48 _
             And Worksheets("Präklinik").Cells(iffunction, 5).Value = geburtsdat _
             And converter = geschlecht _
             And Worksheets("Präklinik").Cells(iffunction, 41).Value = klinik Then
              EINSATZ = Worksheets("Präklinik").Cells(iffunction, 2).Value
              Exit For
          End If
      Next iffunction      
  End Function
  </code></pre>
  
  <p>Please help me diagnose the actual cause of error!</p>