Arrays and Vlookup

bopit87

Member
Joined
Oct 11, 2013
Messages
5
Programming Experience
Beginner
Sorry for the lay out, i am not sure how to get the code to paste properly.

But basically want to search the array for a number entered on the worksheet, if it is there, then enter yes, and if it is not then enter no and have a message pop up sayin that the postcode is invalid.

It only seems to seach the first postcode in the array (800) but will not go through the rest of the list.

Thank you in advance.

VB.NET:
Sub Postcodes()
Dim Postcodes(1464) As Integer
Postcodes(0) = 800
Postcodes(1) = 801
Postcodes(2) = 803
Postcodes(3) = 804
Postcodes(4) = 810
Postcodes(5) = 811
Postcodes(6) = 812
Postcodes(7) = 813
Postcodes(8) = 814
Postcodes(9) = 815
Postcodes(10) = 820.
.
.
.
.
.
Postcodes(1455) = 9017
Postcodes(1456) = 9018
Postcodes(1457) = 9019
Postcodes(1458) = 9020
Postcodes(1459) = 9021
Postcodes(1460) = 9022
Postcodes(1461) = 9023
Postcodes(1462) = 9250
Postcodes(1463) = 9464
Postcodes(1464) = 9729


For i = 0 To UBound(Postcodes)
 If WorksheetFunction.IsNA(Application.VLookup(Range("e1"), Postcodes, 1, False)) = False Then
 Worksheets(1).Range("g1").Value = "Yes"
 
ElseIf WorksheetFunction.IsNA(Application.VLookup(Range("e1"), Postcodes, 1, False)) = True Then
Worksheets(1).Range("g1").Value = "No"
 End If
 Next

If WorksheetFunction.IsNA(Application.VLookup(Range("e1"), Postcodes, 1, False)) = True Then
MsgBox ("This is an invalid postcode.")
End If

End Sub
 
Back
Top