Help with application.match and array

I need to determine what server a spreadsheet is from based on the values in column A. So if column A contains any values in the one of the arrays for the server then we will know what server the spreadsheet is from. When I use this code the values in column A don't matter. Whatever spreadsheet this is used on comes back as server 3 even if column A only has values from a server 1 or server 2 array.

I tried it both with the column and with a range and neither are working. Can someone help me please?





Sub what_is_the_servernumber()
Dim servernumber As Integer


ActiveSheet.Name = "non-gov"
server1 = Array(107, 135, 183, 578, 579, 580, 697, 1022, 1026, 1300, 1463, 1594, 1696)
server2 = Array(287, 305, 311, 620, 1104, 1231, 1670, 208)
server3 = array(172, 209, 218, 232, 473, 605, 606, 607, 608, 610, 719)

If Not IsError(Application.Match(Sheets("non-gov").Columns("A:A"), server1, 0)) Then_ servernumber = 1
If Not IsError(Application.Match(Sheets("non-gov").Columns("A:A"), server2, 0)) Then_ servernumber = 2

If Not IsError(Application.Match(Sheets("non-gov").columns("A:A"), server3, 0)) Then_ servernumber = 3

'If Not IsError(Application.Match(Sheets("non-gov").range("A1:A200"), server1, 0)) Then_ servernumber = 1

If Not IsError(Application.Match(Sheets("non-gov").range("A1:A200"), server2, 0)) Then_ servernumber = 2

If Not IsError(Application.Match(Sheets("non-gov").range("A1:A200"), server3, 0)) Then_ servernumber = 3



Sheets("non-gov").Cells(20, 20).Value = servernumber

End Sub

You cannot use Match to search an array for every value in another array. You can search for a single value.

Try this:

Sub what_is_the_servernumber()
Dim servernumber As Integer


ActiveSheet.Name = "non-gov"
server1 = Array(107, 135, 183, 578, 579, 580, 697, 1022, 1026, 1300, 1463, 1594, 1696)
server2 = Array(287, 305, 311, 620, 1104, 1231, 1670, 208)
server3 = Array(172, 209, 218, 232, 473, 605, 606, 607, 608, 610, 719)

For x = 1 To UBound(server1)
    If Not IsError(Application.Match(server1(x), Sheets("non-gov").Columns("A:A"), 0)) Then servernumber = 1
Next x

For x = 1 To UBound(server2)
    If Not IsError(Application.Match(server2(x), Sheets("non-gov").Columns("A:A"), 0)) Then servernumber = 2
Next x

For x = 1 To UBound(server3)
    If Not IsError(Application.Match(server3(x), Sheets("non-gov").Columns("A:A"), 0)) Then servernumber = 3
Next x

Sheets("non-gov").Cells(20, 20).Value = servernumber

End Sub