Agfdhyk

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.

The first function, upon which all other functions depend on, returns the Patient ID number.

Criteria to select a Patient ID:

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 and time, because from
time to time two patients with the same gender, birthdate and clinic
arrived on the same day.

The function fails after 100000's rows
Beyond this only #VALUE! errors are returned.

Following is a complex scenario I tested, and found the Date and Time to be at fault.

• Comparing only Date, with no interval, returns a normal value.


• 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.


• 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.

Here is the code (pardon any rookie mistakes, I'm no professional coder):

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 < aufnahmdat + 1 / 48 _

           And Worksheets("Präklinik").Cells(iffunction, 4).Value + Worksheets("Präklinik").Cells(iffunction, 17).Value > 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

Please help me diagnose the actual cause of error!