Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming exercise, array
I wish to seek advice on speeding up these processes.
I read a 180,000 lines of text file in an array in Excel 2000. From this 180,000 element array, I used instr method to extract 150,000 elements into a new array. It takes about 2.3 seconds (see code A). With this 150,000 element array, I performed search for keywords 6 times, again using instr method. It takes 16 seconds (see code B). When either the size of the array increases or I need to search for more keywords, the processing time increases to an annoying level. Is there any other way of achieving faster results please? Regards Victor Code A j = 0 For x = 1 To i 'i = 180,000 If InStr(1, arrLarge(x), c, vbTextCompare) 0 Then arrCat(j + 1) = arrLarge(x) j = j + 1 End If Next x Code B For y = 2 To x 'x = 7 thus 6 keywords k = 0 For z = 1 To j 'j = 150,000 If InStr(1, arrCat(z), Cells(c.Row, y), vbTextCompare) 0 Then k = k + 1 Next z If k < 0 Then wsr.Cells(c.Row * 3 - 11, y) = k End If If k = 0 Then wsr.Cells(c.Row * 3 - 11, y) = "NOT found" End If Next y |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lottery exercise | Excel Worksheet Functions | |||
A 13 period exercise | Excel Worksheet Functions | |||
Array Type programming | Excel Programming | |||
Programming Array Formulas in VBA - Can they be intermediate results in RAM? | Excel Programming | |||
Array formulas into VB programming | Excel Programming |