Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 80

Thread: Need help gettin this macro to run faster, Please!

  1. #21
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Yes [aK = Application.WorksheetFunction.Transpose(rK.Value) ]
    I have over 7000 rows of data in Col G and over 400,000 rows of data in col K.
    when I run it i get the run time error 13 mismatch at that line of code.
    meaing when I debug it highlights that line of code.

  2. #22
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Yes [aK = Application.WorksheetFunction.Transpose(rK.Value) ]
    i have 7000 rows of data in col G, and over 400,000 rows of data in col K.
    I run code in box 18 no get run time error 13 type mismatch. Then when I debug it highlights the aK = Application.WorksheetFunction.Transpose(rK.Value)

  3. #23
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Possilbly too much data or bad data (blanks, error message, etc.)

    You'll have to post a workbook with just col G and K to see
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #24
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Ok. When I get home I will post. Thank you

  5. #25
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Ok I just reviewed it looks like too much data can this be fixed to work with this amount of data? I have other Macros that work with large data like this and haven't ran into to this type of error Before. Can we fix this?

  6. #26
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Probably, but it's be easier if you attached a WB with the col G and K data as you have it
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #27
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Ok, thank you

  8. #28
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    hello file is too big, there is a way you can test it,

    open the file Code.xlsm and highlight and the grab bottom right corner of data and drag for instance, in the Column G drag down to 7000 rows, (this will just repeat the data to fill the rows) then do same with Column K drag down to 350,000 rows down then run and you ll see the error. let me know if you can see how we can fix it
    Attached Files Attached Files
    Last edited by estatefinds; 03-10-2017 at 03:36 PM.

  9. #29
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Did you have a chance to read #28?
    Thank you

  10. #30
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    It seems TRANSPOSE has some limits

    I changed macro to not use TRANSPOSE

    Added a FillGK macro to populate 7000 and 350,000 entries in G and K

    macro Match_1 seems to work, but takes awhile

    Option Explicit
    Sub FillGK()
        Dim G As Range, K As Range
        Dim i As Long
        Set G = Range("G1").CurrentRegion
        Set K = Range("K1").CurrentRegion
        
        Application.ScreenUpdating = False
        
        For i = 1 To 7000 \ G.Rows.Count
            Application.StatusBar = "G -- " & i
            G.Copy Range("G1").End(xlDown).Offset(1, 0)
            DoEvents
        Next i
        
        For i = 1 To 350000 \ K.Rows.Count
            Application.StatusBar = "K -- " & i
            K.Copy Range("k1").End(xlDown).Offset(1, 0)
            DoEvents
        Next i
                  
        Application.StatusBar = False
    End Sub



    Option Explicit
    
    Sub match_1()
        Dim rG As Range, rK As Range
        Dim aG As Variant, aK As Variant, aN As Variant
        Dim aG5() As Variant, aK5() As Variant
        Dim G As Long, K As Long, g1 As Long, k1 As Long, n As Long
        
        'setup G's
        Set rG = ActiveSheet.Cells(1, 7)
        Set rG = Range(rG, rG.End(xlDown))
        rG.Interior.ColorIndex = xlColorIndexNone
        aG = rG.Value
        ReDim aG5(LBound(aG, 1) To UBound(aG, 1))
        For G = LBound(aG, 1) To UBound(aG, 1)
            If G Mod 100 = 0 Then
                Application.StatusBar = "Spliting G = " & Format(G, "#,##0")
                DoEvents
            End If
            aG5(G) = Split(aG(G, 1), "-")
        Next G
        
        
        'setup K's
        Set rK = ActiveSheet.Cells(1, 11)
        Set rK = Range(rK, rK.End(xlDown))
        rK.Interior.ColorIndex = xlColorIndexNone
        aK = rK.Value
        ReDim aK5(LBound(aK, 1) To UBound(aK, 1))
        For K = LBound(aK, 1) To UBound(aK, 1)
            If K Mod 100 = 0 Then
                Application.StatusBar = "Spliting K = " & Format(K, "#,##0")
                DoEvents
            End If
            aK5(K) = Split(aK(K, 1), "-")
        Next K
        
        
        'check
        For G = LBound(aG, 1) To UBound(aG, 1)
            For K = LBound(aK, 1) To UBound(aK, 1)
                If K Mod 100 = 0 Then
                    Application.StatusBar = "Checking G = " & Format(G, "#,##0") & " -- K = " & Format(K, "#,##0")
                    DoEvents
                End If
                
                If aG5(G)(4) < aK5(K)(0) Then GoTo NextK    '   largest G < smallest K
                If aG5(G)(0) > aK5(K)(4) Then GoTo NextK    '   smallest G > largest K
                
                n = 0
                For g1 = 0 To 4
                    For k1 = 0 To 4
                        If k1 = 3 And n <= 2 Then Exit For  ' not enougth left
                        If aG5(G)(g1) = aK5(K)(k1) Then
                            n = n + 1
                            If n >= 3 Then              '   Found 3 so mark and get out
                                rG.Cells(G).Interior.Color = vbRed
                                rK.Cells(K).Interior.Color = vbRed
                                Exit For
                            End If
                        End If
                    Next k1
                Next g1
                
    NextK:
            Next K
                
    NextG:
        Next G
        
         
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #31
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Hello I just finished running it it runs good except for:

    now is the Macro only for example: looking at column G at first combination in Cell and then looks in Column K for just 3 matching numbers within the cell?

    cause it looks as if it isnt searching for the 4 numbers matching in the cell of that combination nor high lighting the 5 numbers that match that combination.

    so the 5-11-15-25-30 in column G is found in Column K but not highlighted, and all 5 numbers match.

    so for example it the first combination matches all five numbers of column G to the 5 matching in column K it will highlight, then it will all search if there are 4 matching numbers within the cell in column G that is found in column K it will highlight those cells. and finally if there are at least 3 matching it will highlights those as well.

    can this macro be adjusted?

    Thank you very much for the hard work on this! I give you much Credit on your skills on this!
    Last edited by estatefinds; 03-12-2017 at 11:06 AM.

  12. #32
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I'll check

    I thought that if there were 3 matches, then no point in looking farther since if would be highlighted because of the 3
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #33
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Ok great! So since the 3 matches are colored it will look for what ever 4 matches are left uncolored and highlight if there are matches ,then the 5 matching that's left over uncolored will get highlighted if matched ; just like how the 3 work, but it will be in one code. Thank you very much!!!

  14. #34
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Hello, how is it coming along?
    So the macro would work as it does in #30. The macro, Sub match_1(), the matching 3 numbers runs, Then would run like original code but now seeking the 4 matching numbers of the remaining uncolored combinations , then when that's done it will run for the 5 matching , the run for remaining uncolored ones left over that match.
    Thank you very much for your help on this!
    Last edited by estatefinds; 03-15-2017 at 06:30 AM.

  15. #35
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Been busy with the grandkids

    I'll look tonight
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #36
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    No problem, Thank you

  17. #37
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I tried some test data and it seems to catch the 3, 4, and 5 'matches'

    The logic is that if the macro finds 3, there's no point in continuing to look for 4 or 5

    Look at my made up test data on SHEET2 and see if you can spot a flaw

    Capture.JPG
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #38
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    i looked at it G15 and K4 is a 5 number match, is that the the flaw? its highlighted in g and k so it looks correct.
    Really cant spot a flaw so far.
    Last edited by estatefinds; 03-16-2017 at 05:38 PM.

  19. #39
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by estatefinds View Post
    i looked at it G15 and K4 is a 5 number match, is that the the flaw? its highlighted in g and k so it looks correct.
    Really cant spot a flaw so far.
    I specifically put in some known 3, 4, and 5 matches to test and marked them

    I filled in the rest so there might be a coincidental match that I did not mark

    Keep looking
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  20. #40
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    The only thing i see is that the column K is shorter than column G


    Also I saw
    22-5-14-25-32 was not smallest to largest left to right

Posting Permissions

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