Macros kills performance - how to optimize it?

[da1]

Sheet1!A - Date format
Sheet1!B - Time format
Sheet1!F - Number format
Sheet1!K - Number format

Sheet2!D6 - Date format
Sheet2!D7 - Time format
Sheet2!D8 - Number format

Here is algorithm:
1. Find all lines in Sheet1!A that contains Steet2!D6.
If nothing found Then Sheet2!D8="nothing found", exit macros.

2. In lines from step 1, find line in Sheet1!B that contains Sheet2!D7.
If nothing found Then Sheet2!D8="nothing found", exit macros.

3. If(Sheet1!F(#line from step 2) < Sheet!K(#line from step 2), Sheet2!D8=1, Sheet2!D8=0)<sheet!k(#line from="" step="" 2),="" sheet2!d8="1,"><sheet!k(#line from="" step="" 2),="" sheet2!d8="1,">

Sub macro()
Dim l As Integer

Range("d8") = "": l = 0
Do
l = l + 1
If Sheets(1).Cells(l, "a") = Sheets(2).Range("d6") Then
If Sheets(1).Cells(l, "b") = Sheets(2).Range("d7") Then
If Sheets(1).Cells(l, "f") < Sheets(1).Cells(l, "k") Then Range("d8") = 1 Else Range("d8") = 0
End If
End If
Loop Until (l = Sheets(1).Cells.SpecialCells(xlLastCell).Row + 1 Or Range("d8") <> "")
If Range("d8") = "" Then Range("d8") = "nothing found"


End Sub

Any ideas how can I optimize the code?

Thanks,
Dana
</sheet!k(#line></sheet!k(#line>

 

[da1]

I tried this on a sample file with 40K rows. The introducing the variable alone brought the macro execution time from 11.5 to 1.1 seconds.

Please give your feedback.

Yes, this made a huge difference in processing speed, thank you

 

[poolhall]

thanks for the feedback!

 

[Jerry Sullivan]

I don't understand.. why it doesn't work?

The problem was that I declared both variables as strings.
This caused an incorrect comparison of the date in D6 to those in Column A
(Comparing "1/2/2007" to 39084 returns FALSE.)

The code below changes lValD6 to a Long data type and appears to work.

Sub Button1_Click()
    Dim sFormula As String, sResult As String
    Dim lValD6 As Long, sValD7 As String
    Application.ScreenUpdating = False
    With Sheets(1)
        lValD6 = .Range("D6")
        sValD7 = .Range("D7")
    End With
    With Sheets(2).Range("A:A")
        .Parent.Activate
        With .Range(.Cells(1), .Cells(.Rows.Count, 1).End(xlUp))
            sFormula = "SUMPRODUCT(--(" & .Address & "=" & lValD6 & _
                "),--(" & .Offset(0, 1).Address & "=" & sValD7 & "))"
            If Evaluate(sFormula) Then
                sFormula = "SUMPRODUCT(--(" & .Address & "=" & lValD6 & _
                    "),--(" & .Offset(0, 1).Address & "=" & sValD7 & _
                    "),--(" & .Offset(0, 5).Address & "<" & _
                    .Offset(0, 10).Address & "))"
                If Evaluate(sFormula) Then sResult = 1 Else sResult = 0
            Else
                sResult = "nothing found"
            End If
        End With
    End With
    Sheets(1).Range("D8") = sResult

Sheets(1).Activate
End Sub

 

[da1]

The problem was that I declared both variables as strings.
This caused an incorrect comparison of the date in D6 to those in Column A
(Comparing "1/2/2007" to 39084 returns FALSE.)

The code below changes lValD6 to a Long data type and appears to work.

Sub Button1_Click()
    Dim sFormula As String, sResult As String
    Dim lValD6 As Long, sValD7 As String
    Application.ScreenUpdating = False
    With Sheets(1)
        lValD6 = .Range("D6")
        sValD7 = .Range("D7")
    End With
    With Sheets(2).Range("A:A")
        .Parent.Activate
        With .Range(.Cells(1), .Cells(.Rows.Count, 1).End(xlUp))
            sFormula = "SUMPRODUCT(--(" & .Address & "=" & lValD6 & _
                "),--(" & .Offset(0, 1).Address & "=" & sValD7 & "))"
            If Evaluate(sFormula) Then
                sFormula = "SUMPRODUCT(--(" & .Address & "=" & lValD6 & _
                    "),--(" & .Offset(0, 1).Address & "=" & sValD7 & _
                    "),--(" & .Offset(0, 5).Address & "<" & _
                    .Offset(0, 10).Address & "))"
                If Evaluate(sFormula) Then sResult = 1 Else sResult = 0
            Else
                sResult = "nothing found"
            End If
        End With
    End With
    Sheets(1).Range("D8") = sResult

Sheets(1).Activate
End Sub

Nice, it's working, thanks!

 

[Biz]

The problem was that I declared both variables as strings.
This caused an incorrect comparison of the date in D6 to those in Column A
(Comparing "1/2/2007" to 39084 returns FALSE.)

The code below changes lValD6 to a Long data type and appears to work.

Sub Button1_Click()
    Dim sFormula As String, sResult As String
    Dim lValD6 As Long, sValD7 As String
    Application.ScreenUpdating = False
    With Sheets(1)
        lValD6 = .Range("D6")
        sValD7 = .Range("D7")
    End With
    With Sheets(2).Range("A:A")
        .Parent.Activate
        With .Range(.Cells(1), .Cells(.Rows.Count, 1).End(xlUp))
            sFormula = "SUMPRODUCT(--(" & .Address & "=" & lValD6 & _
                "),--(" & .Offset(0, 1).Address & "=" & sValD7 & "))"
            If Evaluate(sFormula) Then
                sFormula = "SUMPRODUCT(--(" & .Address & "=" & lValD6 & _
                    "),--(" & .Offset(0, 1).Address & "=" & sValD7 & _
                    "),--(" & .Offset(0, 5).Address & "<" & _
                    .Offset(0, 10).Address & "))"
                If Evaluate(sFormula) Then sResult = 1 Else sResult = 0
            Else
                sResult = "nothing found"
            End If
        End With
    End With
    Sheets(1).Range("D8") = sResult
 
Sheets(1).Activate
End Sub

Very nicely done m8

Biz

 

[Jerry Sullivan]

Very nicely done m8

Biz

I credit Mikerickson for his following up a post of mine a few weeks ago and demonstrating how to use Evaluate() in lieu of writing formulas to temporary cells.

I've learned the most on this site when an MVP or other expert follows one of my posts with a better way.

 

[Biz]

I credit Mikerickson for his following up a post of mine a few weeks ago and demonstrating how to use Evaluate() in lieu of writing formulas to temporary cells.

I've learned the most on this site when an MVP or other expert follows one of my posts with a better way.

Can you post link pls?
Probably can speed up my vba too.

Biz

 

[Jerry Sullivan]

Can you post link pls?
Probably can speed up my vba too.

Biz

Here you go, Mate.

http://www.mrexcel.com/forum/showthread.php?p=2831384

 

[Biz]

Thank m8


Biz