faster find method

Hello,

Please look at the screen print attached of the form that I designed...

This is the search screen to search the database (Shared Excel Workbook)

My problem is that every time I search, it takes approximately 3 minutes to produce the results... Please suggest me a faster method to search through the workbook.

Database (Workbook): This is a separate shared excel workbook with 1 worksheet for each user (25 now). Each worksheet contains 45 columns of data

Structure: Everytime a request comes in to the organization,
1. Recorded and stored under the users worksheet as 1 row of data
2. Second person searches for all the records and allocates it to a 3rd person
3. Person 3 searches for all records allocated to him and resolves the request

every action performed on the record creates a new row of data with system date and time stamp on it. Record number is the only UNIQUE IDENTIFIER for all these records (<>0).

Search screen: As you can see from the attached picture, a lot of options are provided to the user to search speccific results.

Current Search method:
1. Load all the existing data to an array - Each column of data is loaded on to a separate array
2. To eliminate the multiple records, date and time stamp is compared and all records but the latest are marked (unique record number is made as 0)
----
Option Base 1
----
For I = 1 to LastRow
For J = I+1 to LastRow
IF UniqueNumberArray(I)=UniqueNumberArray(J) then
if DateTimeArray(I) > DateTimeArray(J) then
DateTimeArray(J)="0"
Else: DateTimeArray(I)="0"
End if
End if
Next J
Next I
----
3. Compare each record to values selected on the search screen with respective array elements
For I = 1 to LastRow
If not UniqueNumberArray(I)= "0" then
If not SourceArray(I) = SelectedSource then UniqueNumberArray(I) = "0"
End if
If not UniqueNumberArray(I)= "0" then
If not TypeArray(I) = SelectedSource then UniqueNumberArray(I) = "0"
End if
Next I

4. Dump array back on to a blank sheet
For I = 1 to LastRow
If not UniqueNumberArray(I)= "0" then
Cells(I,1)=UniqueNumberArray(I)
Cells(I,2)=SourceArray(I)
Cells(I,3)=TypeArray(I)
End if
Next I

5. Load List box with result range
------------------------------------------------------
I know there are multiple For loops which is making the process slow...

I thought of sorting the array to ease the search... but, due to multiple arrays that cannot be possible...

If i make a single multidimentional array instead of multiple single dimentioal array, there is no way to sort a multidimentional array in place (quick sort, sorts only 1 dim array)

I cannot sort and delete rows on a worksheet (instead of arrays) because data from all 25 users exceeds 65000 lines

I need to bring this down to a max search wait time of 10 seconds
------------------------------------------------------
Any help is greatly appriciated....

Please read the forum rules below. Title does amending and code needs to be wrapped

VBA Noob

Hello,

Please look at the screen print attached of the form that I designed...

This is the search screen to search the database (Shared Excel Workbook)

My problem is that every time I search, it takes approximately 3 minutes to produce the results... Please suggest me a faster method to search through the workbook.

Database (Workbook): This is a separate shared excel workbook with 1 worksheet for each user (25 now). Each worksheet contains 45 columns of data

Structure: Everytime a request comes in to the organization,
1. Recorded and stored under the users worksheet as 1 row of data
2. Second person searches for all the records and allocates it to a 3rd person
3. Person 3 searches for all records allocated to him and resolves the request

every action performed on the record creates a new row of data with system date and time stamp on it. Record number is the only UNIQUE IDENTIFIER for all these records (<>0).

Search screen: As you can see from the attached picture, a lot of options are provided to the user to search speccific results.

Current Search method:
1. Load all the existing data to an array - Each column of data is loaded on to a separate array
2. To eliminate the multiple records, date and time stamp is compared and all records but the latest are marked (unique record number is made as 0)
----

Please Login or Register  to view this content.

----
3. Compare each record to values selected on the search screen with respective array elements

Please Login or Register  to view this content.

4. Dump array back on to a blank sheet

Please Login or Register  to view this content.

5. Load List box with result range
------------------------------------------------------
I know there are multiple For loops which is making the process slow...

I thought of sorting the array to ease the search... but, due to multiple arrays that cannot be possible...

If i make a single multidimentional array instead of multiple single dimentioal array, there is no way to sort a multidimentional array in place (quick sort, sorts only 1 dim array)

I cannot sort and delete rows on a worksheet (instead of arrays) because data from all 25 users exceeds 65000 lines

I need to bring this down to a max search wait time of 10 seconds
------------------------------------------------------
Any help is greatly appriciated....

Thanks, But I've got no option but Excel...