Searching an array - SOLVED

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
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 :)
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
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.
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
That is what I would recommend, given my experiences with arrays - tho others may disagree. Do a quick test, and see for yourself!
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870

ADVERTISEMENT

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
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
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:
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,113,986
Messages
5,545,347
Members
410,679
Latest member
rolandbianco
Top