1

First time poster long time reader.

My colleague and I have spent a while creating this code. Whilst it runs brilliantly for small data sizes, our full data set is two tables of 100k lines or so. We let it run for some 30-40 mins and it just grinds to a halt. We have no idea how to make it any faster.

The idea is that for every line in one table, we need to search the second table for a date closest to two days prior to the account date. We also find a date closest to 2 weeks after the date that is two days prior. The dates are sorted newest to oldest from top to bottom.

Once we have this range, we need to search another column to find the first Account ID that appeared within this date range. Once we know this row, we use it to look up two other cells in the row.

I imagine somehow doing it inside an array would be incredibly better but I have no idea how to get it to that level for what we're after. Potentially stick all of the dates within an array and figure out the array number and use those for the rows for the find later on?

Here's our code so far. I know our first problem is possibly because we have a loop that cycles through one table and feeds the account number and date into the function that does the work:

Function Find_Last(AccountNumber, AccountDate As Date)
'Function to find the first occurance of account number and associated quality within a two week range

Dim R As Range
Dim LastDiff1 As Date
Dim LastDiff2 As Date
Dim LastCell1 As Range, LastCell2 As Range
Dim SearchDate1
Dim SearchDate2
Dim Rng As Range
Dim DestSheet As Worksheet
Dim LastRow

Set DestSheet = Workbooks("Interim Referrals Report.xlsm").Worksheets("SA Wrap Up Data")

SearchDate1 = DateAdd("d", 14, AccountDate)
SearchDate2 = DateAdd("d", -2, AccountDate)

LastDiff1 = DateSerial(9999, 1, 1)
LastDiff2 = DateSerial(9999, 1, 1)

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each R In DestSheet.Range("A2:A" & LastRow)
    If IsDate(R.Value) Then
        'Do Nothing
        If Abs(R.Value - SearchDate1) < LastDiff1 Then
            Set LastCell1 = R
            LastDiff1 = Abs(R.Value - SearchDate1)
        End If
    End If
    If IsDate(R.Value) Then
        'Do Nothing
        If Abs(R.Value - SearchDate2) < LastDiff2 Then
            Set LastCell2 = R
            LastDiff2 = Abs(R.Value - SearchDate2)
        End If
    End If
Next R


'Find the CR account number within the designated range in the SA cricket
'data worksheet, looks from bottom of range up
With DestSheet.Range("L" & LastCell1.Row & ":L" & LastCell2.Row)
    Set Rng = DestSheet.Cells.Find(What:=AccountNumber, After:=.Cells(LastCell1.Row), LookIn:=xlFormulas, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
       'if there is a match, return the row number
        If Not Rng Is Nothing Then
            Find_Last = Rng.Row
        Else
            Find_Last = "No Match"
        End If
End With

End Function

Can anyone help?

Improve this question
2
  • Initial and common ideas: switch off temporarily screenupdating... set calculation to manual when code is running. Commented Jul 8, 2014 at 9:55
  • Yeh we did put these in at the start of the code. Looks like it was the looping of actual cells that was slowing it down. Commented Jul 9, 2014 at 1:08

1 Answer 1

Reset to default
1

You are right that changing the loop to use an array will be much faster than looping a range.

Here's a version of your loop using a Variant Array. Untested, but should be close...

Dim Dat As Variant
Dim idx As Long
Dim idxLastCell1 As Long
Dim idxLastCell2 As Long

With DestSheet
    ' start array at row 1 to avoid confusing index offset
    Dat = .Range("A1:A" & LastRow).Value
    idxLastDiff1 = 2
    idxLastDiff2 = 2

    ' Loop from row 2
    For idx = 2 To UBound(Dat, 1)
        If IsDate(Dat(idx, 1)) Then
            If Abs(Dat(idx, 1) - SearchDate1) < Dat(idxLastDiff1, 1) Then
                idxLastCell1 = idx
                LastDiff1 = Abs(Dat(idx, 1) - SearchDate1)
            End If
            If Abs(Dat(idx, 1) - SearchDate2) < Dat(idxLastDiff2, 1) Then
                idxLastCell2 = idx
                LastDiff2 = Abs(Dat(idx, 1) - SearchDate2)
            End If
        End If
    Next
    Set LastCell1 = .Cells(idxLastCell1, 1)
    Set LastCell2 = .Cells(idxLastCell2, 1)
End With

Simply substitute your existing loop with this code. It sets the same variables you use later in your code.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

That's perfect! Thank you so much! The Array looks much better written than I could've done too. I'll give it a go now :D
Just an update. Yesterday our original code ran for over an hour and was still stalled. The new code took 23 Minutes! Success!!
@Shandog great to hear you're happy with the result. But 20+ minutes still seems a long time! There may be other ways to approach this that will be faster still. Eg is your data sorted - may be able to use that to advantage...
The data is sorted chronologically from newest to oldest. As it's essentially a log it needs to be ordered because the timing of events is critical. The file has two tables of maybe 120-140K rows with maybe 10 columns each. So for each row in one table it does the above and cycles through the second table. 20+ is quite long but I imagine 120K multipled by 120K will still take some time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.