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.
Thank you in advance!
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:=xlNoThank you in advance!
