Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
does an item belong to an array?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
does an item belong to an array?
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 --- Regards, Norman "wcc" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
does an item belong to an array?
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 (wcc) wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
does an item belong to an array?
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 -- Dana DeLouis Win XP & Office 2003 "wcc" wrote in message om... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
does an item belong to an array?
Thanks to Norman, keepITcool & Dana.
- wcc "Dana DeLouis" wrote in message ... 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 -- Dana DeLouis Win XP & Office 2003 "wcc" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Second item in list using array | Excel Worksheet Functions | |||
scwewy answerrs that don't belong | Excel Worksheet Functions | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
how to make subs/procs can only be called from within the workbook they belong to? | Excel Programming | |||
Do the Addins belong to a workbook, or are they global to all workbooks loaded? | Excel Programming |