does an item belong to an array?

[wcc]

Hello group,

Beginner here. Is there a function to check if an item belongs to an array?
For exmaple

dim arrStr(0 to 2) as string
dim str as string
arrStr(0) = "Jan"
arrStr(1) = "Feb"
arrStr(2) = "Mar"

str = InputBox("Type month: ")

How do I know if str is a member of arrStr? Do I have to loop through the array?
Thanks for your attention.

- wccppp

 

[Norman Jones]

Hi WCC,

You could use the worksheet match function:

Sub Tester01()
Dim sStr As String
Dim arrStr(0 To 2) As String
Dim res As Variant

arrStr(0) = "Jan"
arrStr(1) = "Feb"
arrStr(2) = "Mar"

sStr = InputBox("Type month: ")

On Error Resume Next
res = Application.Match(sStr, arrStr, 0)
If Not IsError(res) Then
'Take appropriate action
MsgBox "Found!"
Else
'Take appropriate alternative action
MsgBox "Invalid value"
End If
On Error GoTo 0
End Sub

 

[keepITcool]

you can use
application.worksheetfunction.Match

Sub foo()
Dim sRes As String, vArr As Variant, vChk As Variant
'split only works in xl2000 and newer
vArr = Split("Jan;Feb;Mar", ";")
ask:
sRes = InputBox("Type month (3 letters): ")
If sRes <> "" Then
On Error Resume Next
vChk = WorksheetFunction.Match(sRes, vArr, 0)
On Error GoTo 0
If Not IsEmpty(vChk) Then
MsgBox "You can type!"
Else
GoTo ask
End If
End If
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dana DeLouis]

If you would like to check all months against either the short and long
month name, here's a similar idea.

Sub Valid_Month()
Dim v1, v2
Dim Chk As Long
Dim sRes As String
Const msg1 As String = _
"Type month (3 letters,or full month): "

With Application
v1 = .GetCustomListContents(3)
v2 = .GetCustomListContents(4)
End With

sRes = InputBox(msg1)
If sRes = vbNullString Then Exit Sub

On Error Resume Next
With WorksheetFunction
Chk = 0 'False
Chk = .Match(sRes, v1, 0)
Chk = Chk + .Match(sRes, v2, 0)
End With

If Chk Then
MsgBox "Valid"
Else
MsgBox "Not Valid", vbExclamation
End If

End Sub

HTH

 

[wcc]

Thanks to Norman, keepITcool & Dana.

- wcc

If you would like to check all months against either the short and long
month name, here's a similar idea.

Sub Valid_Month()
Dim v1, v2
Dim Chk As Long
Dim sRes As String
Const msg1 As String = _
"Type month (3 letters,or full month): "

With Application
v1 = .GetCustomListContents(3)
v2 = .GetCustomListContents(4)
End With

sRes = InputBox(msg1)
If sRes = vbNullString Then Exit Sub

On Error Resume Next
With WorksheetFunction
Chk = 0 'False
Chk = .Match(sRes, v1, 0)
Chk = Chk + .Match(sRes, v2, 0)
End With

If Chk Then
MsgBox "Valid"
Else
MsgBox "Not Valid", vbExclamation
End If

End Sub

HTH