Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel VBA - using arrays

Posted on 2011-02-16
Medium Priority
373 Views
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
0
Question by:matt_m
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2
• +3

LVL 50

Assisted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 400 total points
ID: 34911939
Hello,

you could loop through the array

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)

For i = LBound(fund) to UBound(fund)

If code = fund Then

Call Matcher

End If

Next i

Next x

(not tested)

cheers, teylyn

0

LVL 50

Expert Comment

ID: 34911956
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 http://www.experts-exchange.com/A_2684.html may help

Happy to code this if you provide a sample

Cheers

Dave
0

LVL 23

Assisted Solution

Michael Fowler earned 400 total points
ID: 34912027

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

If you make the array items above the keys and just add a dummy value you can then use the exists method to check if the string is in the dictionary.

eg

Dim MyDictionary As Object
Set MyDictionary = CreateObject("Scripting.Dictionary")
.
.
.

For x = 1 To Lastrow
If MyDictionary.Exists(Sheets("Dimension").Cells(x, 1)) Then Call Matcher
Next

Michael
0

LVL 50

Accepted Solution

Dave Brett earned 800 total points
ID: 34912052
Working completely with arrays (for both cell test range and for Fund strings) you could do this to optimise speed

1) Assumed you range data is in column A
2) Sets an array equal to column A range
3) Test whether this array matches any string in the 8 string Fund array
4) Gives a MsgBox if True (replace with your call here)

Cheers

Dave
``````Sub Chck()
Dim rng1 As Range
Dim X
Dim fund As Variant
Dim lngrow As Long
Dim lngMatch As Long

fund = Array("GCEP", "GHYFND", "GREP", "GVEP", "WEMP", "WSSP", "GGEP", "GTRFND")
Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
X = rng1

For lngrow = 1 To UBound(X)
On Error Resume Next
lngMatch = Application.WorksheetFunction.Match(X(lngrow, 1), fund, 0)
On Error GoTo 0
If lngMatch > 0 Then MsgBox "Call for " & X(lngrow, 1)
Next
End Sub
``````
0

LVL 45

Assisted Solution

patrickab earned 400 total points
ID: 34912125
matt_m,

In the attached file is the code below. Press the button on Sheet1 to run the macro.

You will see in the code that Split automatically creates a zero based array which can be indexed easily. That is the easiest way to create and array or elements which can be given as a simple string - being the easiest format to create in the first place.

Of course instead of the MsgBox you could Call another subroutine if a match is found.

Hope it helps explain in some small way how arrays can be used.

Patrick
``````Sub tester()
Dim str1 As String
Dim str2 As String
Dim fund() As String
Dim suffix() As String
Dim code As String
Dim x As Long

str1 = "GCEP,GHYFND,GREP,GVEP,WEMP,WSSP,GGEP,GTRFND"
str2 = "t,st,nd,rd,th"
fund = Split(str1, ",")
suffix = Split(str2, ",")

For x = 0 To UBound(fund)
If fund(x) = Sheets("Sheet1").Cells(x + 1, 1) Then
MsgBox "Yep, it matched the " & x & suffix(IIf(x < 4, x, 4)) & " element of the fund array"
End If
Next x

End Sub
``````
arrays-02.xls
0

Author Comment

ID: 34912137
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.
0

Author Closing Comment

ID: 34914064
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
0

LVL 45

Expert Comment

ID: 34914446
Matt - Thanks for the points - Patrick
0

LVL 93

Expert Comment

ID: 34921116
matt_m,

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
``````

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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month8 days, 23 hours left to enroll