Run-time error1004  in code when searching through large data set
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Run-time error1004 in code when searching through large data set
Thanks Thanks: 0 Likes Likes: 0

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

    Default Run-time error1004 in code when searching through large data set

    Hello,

    I have a command button in my workbook that runs code which searches through a column of data (each cell in the column is text inputted in the form of a few sentences) and creates a word count on another sheet in the workbook. The code is written so that every time if finds a new word it is added to the new sheet and it counts how many time that word appears in the column. It then sorts the newly created word count table in order from most occurring word to least occurring. This code runs for two different columns ("Failure" column and "Maintenance Action" column).

    The code works great until the data set is around 12,000+ cells long. When it is this long I get an error of the following type: "Run-time error '1004': Application-defined or object-defined error." When I click "Debug" it highlights the line "Sheets("Maintenance Action Word Counts").Range("A1").Resize(.Count, 2).Value = Application.Transpose(Array,.keys,.items))" It never has an error on the Failure Word Count which is of the same length as the Maintenance Action Word Count.

    Please look at the code below and let me know if you have any suggestion or further questions.

    Code:
        'Failure COUNTS CODE
        Dim Ary As Variant, Sp As Variant
        Dim I As Long, j As Long
       
        With Sheets("Word Search")
            Ary = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value2
        End With
        With CreateObject("scripting.dictionary")
            For I = 1 To UBound(Ary)
                Sp = Split(Ary(I, 1))
            For j = 0 To UBound(Sp)
                .Item(Sp(j)) = .Item(Sp(j)) + 1
            Next j
            Next I
                Sheets("Failure Word Counts").Range("A1").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
        End With
        
        'Sort in order from largest to smallest
        LastRow = Worksheets("Failure Word Counts").Range("B" & Rows.Count).End(xlUp).Row
        Worksheets("Failure Word Counts").Range("A1:B" & LastRow).Sort _
        key1:=Worksheets("Failure Word Counts").Range("B:B"), order1:=xlDescending, Header:=xlNo
        
    '-----------------------------------------------------------------------------------------------------
        'Maintenance Action COUNTS CODE
        With Sheets("Word Search")
            Ary = .Range("D2", .Range("D" & Rows.Count).End(xlUp)).Value2
        End With
        With CreateObject("scripting.dictionary")
            For I = 1 To UBound(Ary)
                Sp = Split(Ary(I, 1))
            For j = 0 To UBound(Sp)
                .Item(Sp(j)) = .Item(Sp(j)) + 1
            Next j
            Next I
    'CLICKING DEBUG HIGHLIGHTS THE FOLLOWING LINE
                Sheets("Maintenance Action Word Counts").Range("A1").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
        End With
        
        'Sort in order from largest to smallest
        LastRow = Worksheets("Maintenance Action Word Counts").Range("B" & Rows.Count).End(xlUp).Row
        Worksheets("Maintenance Action Word Counts").Range("A1:B" & LastRow).Sort _
        key1:=Worksheets("Maintenance Action Word Counts").Range("B:B"), order1:=xlDescending, Header:=xlNo
    Thank you in advance!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Run-time error1004 in code when searching through large data set

    If you put this just before the line that fails, what does the message box say?
    Code:
    Msgbox .Count
    Also what version of Xl are you using?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error1004 in code when searching through large data set

    When I insert that code right before the line that fails I get "11,599"

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

    Default Re: Run-time error1004 in code when searching through large data set

    I'm using Excel 2013.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Run-time error1004 in code when searching through large data set

    In that case I'm not sure why you would get an error. Transpose should be able to handle that amount without any problems.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error1004 in code when searching through large data set

    Could it have anything to do with using I and j or Ary as variables in both For loops? Do I need to switch variables in the Maintenance Action Count For loop?

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Run-time error1004 in code when searching through large data set

    Does this work
    Code:
        With CreateObject("scripting.dictionary")
            For i = 1 To UBound(Ary)
                Sp = Split(Ary(i, 1))
            For j = 0 To UBound(Sp)
                .item(Sp(j)) = .item(Sp(j)) + 1
            Next j
            Next i
            ReDim Ary(1 To .Count, 1 To 2)
            For i = 0 To .Count - 1
                Ary(i + 1, 1) = .Keys()(i)
                Ary(i + 1, 2) = .items()(i)
             Next i
                Sheets("Maintenance Action Word Counts").Range("A1").Resize(.Count, 2).Value = Ary
        End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Jun 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error1004 in code when searching through large data set

    I get the same error with that code. It works great every single time for the Failure Word Count which has the same amount of data. I've ran it with other sets of data containing 36,000 lines and I get the same error but the message box says 24,000 instead of 11,599

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Run-time error1004 in code when searching through large data set

    Do you get the error on the same line?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Jun 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error1004 in code when searching through large data set

    Yes the error shows up on the same line. The msgbox just displays a different value.

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
  •