VBA Conditional Stmt Question (IN operator)

[jhrBanker]

I'm trying to check for multiple values in an IF statement in vba, but I'm
receiving error messages:
If Mid(InputString, 54, 3) IN('070', '111', '305', '345', '456') Then
Statement1
Else
Statement2
End If

I've tried double-quotes in place of single-quotes, but that didn't work.
Any help will be appreciated.

 

[Svetlana]

You should make it like this:
If Mid(InputString, 54, 3)="070" Or Mid(InputString, 54, 3)=111 Or
etc.. Then
Statement1
Else
Statement2
End If

 

[Stefan Hoffmann]

hi,

I'm trying to check for multiple values in an IF statement in vba, but I'm
receiving error messages:
If Mid(InputString, 54, 3) IN('070', '111', '305', '345', '456') Then
Statement1
Else
Statement2
End If

You can also use

Select Case Mid(InputString, 54, 3)
Case "070", "111", "305", "345", "456"
Statement1
Case Else
Statement2
End Select


mfG
--> stefan <--

 

[Klatuu]

IN is an SQL predicate, not a VBA function.
This is better than a string of If Else

Select Case Mid(InputString, 54, 3)
Case "070", "111", "305", "345", "456"
Statement 1
Case Else
Statement 2
End Select

 

[JethroUK©]

If INSTR("070,111,305,345,456", Mid(InputString, 54, 3)) Then

Else

EndIF

 

[Stefan Hoffmann]

hi,

If INSTR("070,111,305,345,456", Mid(InputString, 54, 3)) Then

It's not a good idea, because it will also be True for "0,1" etc.

mfG
--> stefan <--

 

[JethroUK©]

but that will never be found - hence i included the comma seperator - i will
work perfectly

 

[Stefan Hoffmann]

hi,

but that will never be found - hence i included the comma seperator - i will
work perfectly

You have no control over InputString, hence Mid(InputString, 54, 3) may
return "0,1", thus the false positive.


mfG
--> stefan <--

 

[JethroUK©]

there is considerably less chance of someone typing '0,1' by mistake than
typing '305' by mistake

by 'any' method they'll get the wrong result - hence i would still use:

If INSTR("070,111,305,345,456", Mid(InputString, 54, 3)) Then

 

[Douglas J. Steele]

To handle Stefan's concern, use

If INSTR(",070,111,305,345,456,", "," & Mid(InputString, 54, 3) & ",") Then

 

[david epsom dot com dot au]

'In' is only available from the expression evaluator:

If eval("Mid('" & InputString & "', 54, 3) IN('070', '111', '305', '345',
'456')") Then

CASE is a better approach.

(david)