Searching an array - SOLVED

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
I have an array set up called arrData(1 to 16, 1 to 393). The second element of the array is variable - usually between 100 and 500.

The array contents are in this format:
  • arrdata(1,1) : 100075503 : Variant/Double
    arrdata(1,2) : 100075610 : Variant/Double
    arrdata(1,3) : 100078591 : Variant/Double

    arrdata(2,1) : "Fred Smith" : Variant/String
    arrdata(2,2) : "Tom Jones" : Variant/String
    arrdata(2,3) : "Eric Clapton" : Variant/String

    arrdata(3,1) : "Ld Team-Basildon" : Variant/String
    arrdata(3,2) : "Ld Team-Basildon" : Variant/String
    arrdata(3,7) : "Ld A&T-Colchester" : Variant/String

with elements 4 to 16 being empty.

Code:
            Select Case wrkSht.Name
                Case "1.a"
                For iCntr3 = iFirstTeamRow To iLastTeamRow
                    Set c = arrdata(1) _
                        .Find(wrkSht.Cells(iCntr3, 2), LookIn:=xlValues)
                Next iCntr3
            End Select

What I'm trying to do with the code above is to return the ID number from the worksheet and find where it is in the array, hopefully without having to search through the array one element at a time.

So if wrkSht.Cells(iCntr3,2) held 100078591, then C (in the above code) would return 3 - i.e. arrData(1,3).

How do I do this? I pretty much knew the code above wouldn't work, but it was worth a try :)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Arrays are notoriously fast to search through, and with the size you specified its probably not worth the effort in writing a funky search algorithm when you can just traverse it in a linear fashion....

just my 20 cents
 
Upvote 0
Thanks for the fast reply Patrick.

I wasn't sure if there was a built in search array type function.
I'll just use a For...Next loop if it won't impact on performance too much.

Thanks again.
 
Upvote 0
That is what I would recommend, given my experiences with arrays - tho others may disagree. Do a quick test, and see for yourself!
 
Upvote 0
Will do. I've never timed one of my procedures before so it's about time I gave it a go.
Saying that though, even if it takes an hour to run that still makes it about 7 hours faster than the old way the office did this task (Ctrl+C, Ctrl+V a few thousand times).

Still proves I'm the miracle worker in my office - they ask for a miracle and I make a beam of sunlight shoot from their backside into the bosses eyes. :LOL:
 
Upvote 0
I know what you mean mate, it never ceases to amaze me how poor the majority of people in offices are at even basic computing tasks...

One boss I worked for actually almost died when I set up a DDE link between excel and their in-house system to automate order processing. The really worrying thing was the team of 5 IT bods had never even noticed you could do it despite having ownership of the software for about 6-7 years. I mean, its plainly spelt out how to do it in the help files of the host application, it couldnt be more obvious!!! So even the IT bods can be utterly shocking.

Anyway, rant over - have fun!
 
Upvote 0
Hi

Looking at the code you posted, it looks like you'd be better off with an array of lookup dictionaries. Of course, since I don't see the rest of the code this may make no sense.


I post a small example, just in case:

Code:
Sub TestDic()
Dim dic As Object

Set dic = CreateObject("Scripting.Dictionary")
dic.Add 100075503, 1
dic.Add 100075610, 2
dic.Add 100078591, 3

If dic.Exists(100078591) Then MsgBox dic(100078591)
    
End Sub

kind regards
PGC
 
Upvote 0
they ask for a miracle and I make a beam of sunlight shoot from their backside into the bosses eyes.

Thanks for the image...
:biggrin:
 
Upvote 0
Thanks for the alternative possibility pgc01.
I haven't used your example in this case (looping through didn't make any noticable impact on speed) but it looks like it would work, so I'll add it to my ever increasing knowledge base :)

Always glad to be of service dcardno :devilish:
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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