Vba loading slow while using for loop & if condition

[senthil2406]

Private Sub CommandButton1_Click()
Dim LastRow As Long, LR As Long
For x = 6 To LR
For y = 7 To LastRow
If Sheets("Master").Range("L4") = Sheets("In & Out Data").Cells(y, 5) And Sheets("Master").Range("D6") = Sheets("In & Out Data").Cells(y, 7) Then
Range("L" & x) = WorksheetFunction.SumIf(Sheets("In & Out Data").Range("G7:G1500"), Sheets("Master").Range("D" & x), Sheets("In & Out Data").Range("K7:K1500"))
End If
Next y
Next x

End Sub

 

[Michael M]

you haven't determined what LR and LastRow are..usually done by
But why do you need and "x" AND a "y"

In your code y could be replaced by x+1 in the equation

Dim LastRow As Long, LR As Long
lr = cells(rows.count,"A").end(xlup).row
LastRow = cells(rows.count,"B").end(xlup).row
For x = 6 To LR
For y = 7 To LastRow

 

[Fluff]

Hi & welcome to MrExcel.
Along with what Michael M has said, the loops makes no sense.
If this line returns true when y=7

Sheets("Master").Range("L4") = Sheets("In & Out Data").Cells(y,  5) And Sheets("Master").Range("D6") = Sheets("In & Out  Data").Cells(y, 7)

This line will then calculate for every value of x

Range("L" & x) = WorksheetFunction.SumIf(Sheets("In & Out  Data").Range("G7:G1500"), Sheets("Master").Range("D" & x),  Sheets("In & Out Data").Range("K7:K1500"))

so the only result you will get is for x=LR

Can you please explain what you are trying to do?

 

[senthil2406]

Hi Michael,

Thanks for your reply, here full program for your reference.
the purpose of code is the if condition is matched value has to sum, the function between 2 sheets.

I hope its more clear for you.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Private Sub CommandButton1_Click()
        For x = 6 To 3500
        For y = 7 To 300
                If Sheets("Master").Range("L4") = Sheets("In & Out Data").Cells(y, 5) And Sheets("Master").Range("D6") = Sheets("In & Out Data").Cells(y, 7) Then
                        Range("L" & x) = WorksheetFunction.SumIfs(Sheets("In & Out Data").Range("K7:K300"), Sheets("In & Out Data").Range("G7:G300"),          Sheets("Master").Range("D" & x), _
                        Sheets("In & Out Data").Range("E7:E300"), Sheets("Master").Range("L4"))
                End If
                    
                        If Sheets("Master").Range("N4") = Sheets("In & Out Data").Cells(y, 5) And Sheets("Master").Range("D6") = Sheets("In & Out Data").Cells(y, 7) Then
                            Range("N" & x) = WorksheetFunction.SumIfs(Sheets("In & Out Data").Range("K7:K300"), Sheets("In & Out Data").Range("G7:G300"), Sheets("Master").Range("D" & x), _
                            Sheets("In & Out Data").Range("E7:E300"), Sheets("Master").Range("N4"))
                        End If
        
                            If Sheets("Master").Range("L4") = Sheets("In & Out Data").Cells(y, 5) And Sheets("Master").Range("D6") = Sheets("In & Out Data").Cells(y, 7) Then
                                Range("M" & x) = WorksheetFunction.SumIfs(Sheets("In & Out Data").Range("L7:L300"), Sheets("In & Out Data").Range("G7:G300"), Sheets("Master").Range("D" & x), _
                                Sheets("In & Out Data").Range("E7:E300"), Sheets("Master").Range("L4"))
                            End If
                              
                                If Sheets("Master").Range("N4") = Sheets("In & Out Data").Cells(y, 5) And Sheets("Master").Range("D6") = Sheets("In & Out Data").Cells(y, 7) Then
                                    Range("O" & x) = WorksheetFunction.SumIfs(Sheets("In & Out Data").Range("L7:L300"), Sheets("In & Out Data").Range("G7:G300"), Sheets("Master").Range("D" & x), _
                                    Sheets("In & Out Data").Range("E7:E300"), Sheets("Master").Range("N4"))
                                End If
Next y
Next x
End Sub