Results 1 to 4 of 4

Thread: Vba loading slow while using for loop & if condition
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vba loading slow while using for loop & if condition

    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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,805
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Vba loading slow while using for loop & if condition

    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
    Code:
    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
    Last edited by Michael M; Jun 15th, 2019 at 03:13 AM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vba loading slow while using for loop & if condition

    Hi & welcome to MrExcel.
    Along with what Michael M has said, the loops makes no sense.
    If this line returns true when y=7
    Code:
    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
    Code:
    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vba loading slow while using for loop & if condition

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

    Code:
    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
    Last edited by Fluff; Jun 16th, 2019 at 07:28 AM. Reason: code tags

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •