VBA code for last cell that meets certain criteria?

Hi!

I'm writing a code that's having problems and I believe its due to the last row number being too large. Every day a new file is generated with data that's organized from oldest to furthest future work (i.e. the first entry is June 2011, and the last entry is October 2012). My code generates a column with values ranging from 0 to 3 based on the entry's date and today. For example:

Col H - Due Date	Col N - Unique Identity	Col AA - Due Date Category
June 15, 2011	3000215	1
May 23, 2012	3000305	2
Jun 15, 2012	3000423	3
Aug 10, 2012	3000513	0

0 = beyond current work scope, 1 = Late, 2 = Due before work scope ends, 3 = Due at end of work scope

I'm using a For/Next loop to create an array that retrieves data with a formula and stores the values in a column (for a later calculation), and it stores the data based on if the data is a 1, 2, or 3. At the moment, I'm just working on the "1", or late work, which will store in col AB. When I ran the code Excel crashed, and I think its because there's 3000 records to look at?

Currently my code is this:

Option Explicit

Public Sub Demand()

Dim iCount As Integer
Dim lngLastRow As Long
Dim rngORD As Range
Dim rngArrayStatus As Range
Dim rngArrayUniqueBefore As Range

' Code to determine last row with data
Set rngORD = Worksheets("REPORT").Range("H5")
lngLastRow = Cells(6536, rngORD.Column).End(xlUp).Row
Set rngORD = Range("H5:H" + Trim(Str(lngLastRow)))

' A section of my code that's unrelated to the problem

' Code to generate a numerical representative of the status of the data as of today
Set rngArrayStatus = Worksheets("REPORT").Range("AA5:AA" + Trim(Str(lngLastRow)))
rngArrayStatus.FormulaR1C1 = _
    "=IF(ISBLANK(RC[-19]),"""",IF(RC[-19]-TODAY()<0,1,IF(RC[-19]<DATE(2012,6,15),2,IF(RC[-19]=DATE(2012,6,15),3,0))))"

' Generate an array with only the identities for entries that are late (aka "1")
Set rngArrayUniqueBefore = Range("AB5:AB" + Trim(Str(lngLastRow)))
For iCount = 1 To lngLastRow - 1
     rngArrayUniqueBefore.Cells(1, 1).FormulaArray = _
          "=IF(ROWS(R5C[-27]:RC[-27])<=R2C28,INDEX(R5C[-14]:R1732C[-14],SMALL(IF(R5C27:R1732C27=R1C28,ROW(R5C27:R1732C27)-ROW(R5C27)+1),ROWS(R5C[-27]:RC[-27]))),"""")"
Next iCount

' Rest of the code that hasn't been fixed yet

End Sub()

I'm thinking I should replace lngLastRow in my rngArrayUniqueBefore with the last row number of the last row with a "1" in it. I tried figuring out a code that would search upward in column AA until it found a 1 and then return the row number and store it as lngLastRow1, but I'm fairly new at VBA and I'm at a lost at how to do this. I tried rngArrayStatus.Find but I wasn't able to get it to work. If this was successful, then it would only need to look at 200 records instead of 3000 (approximately).

My question is how do I do this? Also, if anyone sees another reason why this may have crashed, then I greatly appreciate the catch!!

Thanks in advance!