Using the Find in VBA vs using arrays

jpynn

New Member
Joined
Mar 24, 2004
Messages
23
Hi All,

I was wondering if anyone had any idea which method of finding data is faster

Scenario 1: Using arrays

Select the source data sheet

Input all data into arrays (using a while loop)

Find data by using a for..next loop to check each element until a match is found
.


Scenario 2: Using the Find function

Select the source data sheet

Use the find method to extract required info.


To me it would seem that the find function is superior. Especially when the required data/total data ratio is low. However for the application I am designing most of the source data is required (but not all, and not in the same order).

Hoping there is someone out there who has had experience with using both and can recommend which one is better.

thanks.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
For scenario one, I wouldn't to read data from the sheet to the array.
Code:
Dim myArray as Variant
myVariant = myRange.Value
will quickly load myArray with the data in myRange (any size) without looping.

Many of the MATCH type functions work on arrays. If the data is sorted, there are search techniques better than brute force looping.

.Find is good, but I think the preferenece is situational. (for example, what is going to be done once the item is found.)
 
Upvote 0
Thanks for the Replies! :biggrin:

To answer Norie's question, yes that would be two loops, although by mike's response for loops might be a completely obsolete method of gathering the data.

mike does the mean the reverse is true, that I can dump an entire array to a workbook using similar code? because not writing for loops to dump the results would save a bunch of run time.
 
Upvote 0
Yes, but you need to specifically choose a worksheet range of the same size/dimensions as the array for the assignment - ie you can't just go:

Range("A1").Value = myVarArray

you need:

Range("A1:D50").Value = myVarArray

(or whatever is appropriate).
 
Upvote 0
It's already been mentioned by mikerickson :-

It depends upon what you intend to do with the data once found.

Also depends on size, format, content, etc. of your worksheet.

Basically, there is no one method that is better(faster) than all other methods for all situations.

(Also, looping through an array is normally much more efficient than looping through worksheet objects)
 
Upvote 0
Again thanks for the quick replies!!

Very useful stuff.

Boller, the last line of your post was the answer to the essence of the question I wanted to ask, though I didn't really know how to formulate it properly.

In any case I have learned a new trick. For that I am most greatful
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top