Lookup using match in named range stored in array

Hi,

I am trying to look up a value in one array in a named range, and if it is recognized, perform certain action. This is done in a loop. However, instead of repeating the procedure with different named ranges (hardcoding) i have tried to save the names of the named ranges in an array and loop thorugh them(Dont mind the j):

Please Login or Register  to view this content.

This doesnt work?!
Have you any idea what is wrong?

A couple of things appear to need amendment:

1. The Match function is a subset of the Application.WorksheetFunction object, not the Application object.

2. When using the match function within an Excel worksheet the function returns #NA if a match is not found, but within the VBA version a run-time error occurs, which has to be 'trapped'.

See the following suggested changes...


For i = LBound(UniqueCountries) To UBound(UniqueCountries)
For k = 1 To 17
On Error GoTo Not_Found
If Application.WorksheetFunction.Match(UniqueCountries1(i, 1), RegionsNames(k, 1), 0) > 0 Then
Subs(j, 1) = Subs(j, 1) + Country(j)(i)
End If
Not_Found:
On Error GoTo 0
Next k
Next i

See if this gets the code working.

Hi Loz,

Thank you for the suggestions.

I forgot to mention that this line,:

Please Login or Register  to view this content.

works perfectly if instead of matching with an array, i just write for instance:

Please Login or Register  to view this content.

Where "NorthAmerica", is an array of countries associated with it.
However, if i loop through these arraynames, i get the "Subscript out of order"
error.
I don't know why the match function can't use a string variable to tell it where to lookup.

The example code you provided didn't explain how you had dimensioned the arrays, or how you had assigned the values to the elements. I've therefore tested the code and made alterations, as follows....

Option Base 1
Sub Test()
Dim UniqueCountries1(3) As String, RegionsNames(3) As String
UniqueCountries1(1) = "USA"
UniqueCountries1(2) = "Brazil"
UniqueCountries1(3) = "Canada"
RegionsNames(1) = "USA"
RegionsNames(2) = "Alaska"
RegionsNames(3) = "Mexico"
For i = LBound(UniqueCountries1) To UBound(UniqueCountries1)
On Error GoTo Not_Found
If Application.WorksheetFunction.Match(UniqueCountries1(i), RegionsNames, 0) > 0 Then
Subs(j, 1) = Subs(j, 1) + Country(j)(i)
End If
Not_Found:
On Error GoTo 0
Next i
End Sub

First important point is that it is always advisable to declare whether array subscripts will start at 0 or 1 - hence the Option Base 1 item declares that the arrays will start at 1.

Secondly, for an array of country or region names, it is only necessary to have a single dimension and a single subscript, therefore UniqueCountries1(i) should be used instead of UniqueCountries1(i,1). The latter is probably where the "subscript out of order" error occurs.

The Match function expects to have an array of values for its 2nd argument, therefore simply referring to the RegionsNames array variable should be correct, rather than needing to loop through each element with the For k = 1 to 17 code.

If using arrays is a bit muddling, you can use a simple string variable that contains all the RegionsNames e.g.


Dim strNames as String
Let strNames = "USA Canada Brazil" 'etc

and then use the InStr function....

If InStr(strNames, UniqueCountries1(i)) > 0 then
' items found
Else
' items not found
End If

Hope these additional notes get your code working!