Link to home
Start Free TrialLog in
Avatar of matt_m
matt_m

asked on

Excel VBA - using arrays

Hi Experts,

What I need to do is to loop through a range and if a cell matches any one of 8 strings to call another procedure.  I had it working fine with a Select Case type approach but I was wondering if I could put the 8 funds into an array and then compare the array variable to the cell - something like the below (which doesn't work!).  One ofmy motivaitons for asking this question is that I don't use arrays at all in my code at the moment so I'm looking to learn as much about them as possible.

Dim fund As Variant

fund = Array("GCEP", "GHYFND", "GREP", "GVEP", "WEMP", "WSSP", "GGEP", "GTRFND")

For x = 1 To Lastrow

code = Sheets("Dimension").Cells(x, 1)
   
    If code Like fund Then

    Call Matcher

    End If

Next x


Cheers
Matt
SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Matt,

I would reverse the logic of the approach

1) Set up an array of cells that matches your range
2) loop through the array of cells to be check (much faster than a range) then run say a MATCH formula on an 8 cell list (GCEP, GREP etc) to determine in a single shot if the cell matches any of the strings

My Variant Array artivle at https://www.experts-exchange.com/A_2684.html may help

Happy to code this if you provide a sample

Cheers

Dave
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of matt_m
matt_m

ASKER

Hi All, thanks so much for your help - they all look like great ideas and if I can't use them in this situation I'm sure I'll find a use for them later.  I'll work through each one today and come back later to give out points.

Thanks again.
Avatar of matt_m

ASKER

Hi All, I ended up using Brett's approach.  Everyone else's ideas though were so fantastic though I shared the points around (hope thats ok!).

Cheers
Matt
Matt - Thanks for the points - Patrick
matt_m,

Glad to see you found your answer, and especially glad to see that Michael recommended my Dictionary article :)

For a relatively small list, I would skip the arrays/dictionaries altogether and just make a concatenated list.  For example:


Const fund As String = "||GCEP||GHYFND||GREP||GVEP||WEMP||WSSP||GGEP||GTRFND||"

For x = 1 To Lastrow

    code = "||" & Sheets("Dimension").Cells(x, 1) & "||"
   
    If InStr(1, fund, code) > 0 Then

        Call Matcher

    End If

Next x

Open in new window



Note that for this to work, the delimiter must be applied to the beginning and end of the concatenated string, and that it should be a character or combination of characters that is unlikely to appear in any of the members of the concatenated list.

Patrick