vba If condition help..

[Mallesh23]

Hi Team,


Is there a way to shorten this code used in if condition.
If Sheet1.Cells(i, 1).Value = "Green" Or Sheet1.Cells(i, 1).Value = "Red" Or Sheet1.Cells(i, 1).Value = "Blue" Then

like If Sheet1.Cells(i, 1).Value = Array("Green", "Red", "Blue") Then


Thanks in advance



Regards,
mg

 

[Peter_SSs]

There are many ways and the best one will depend on your individual circumstances (including for example whether the case of the text has to match exactly) & your preference but here are 3 ways to consider

If IsNumeric(Application.Match(Sheet1.Cells(i, 1).Value, Array("Green", "Red", "Blue"), 0)) Then
  MsgBox "Yes"
Else
  MsgBox "No"
End If


If InStr(1, "|Green|Red|Blue|", "|" & Sheet1.Cells(i, 1).Value & "|", 1) > 0 Then
 MsgBox "Yes"
Else
  MsgBox "No"
End If


Select Case LCase(Sheet1.Cells(i, 1).Value)
  Case "green", "red", "blue"
    MsgBox "Yes"
  Case Else
    MsgBox "No"
End Select

 

[mohadin]

Hi
what about

If Sheet1.Cells(1, 1).Value = Green Or Red Or Blue Then

 

[Peter_SSs]

Hi
what about

If Sheet1.Cells(1, 1).Value = Green Or Red Or Blue Then

Did you try that?

 

[mohadin]

Yep I did

 

[Peter_SSs]

Yep I did

Well in that case you would have found that it does not work.

 

[mohadin]

Nop
It works OK

 

[Peter_SSs]

Nop
It works OK

Really?? I think you should check again.

Here's my test of your code line

Sub Test()
  Dim i As Long
  
  For i = 1 To 3
    If Sheet1.Cells(i, 1).Value = Green Or Red Or Blue Then
      Sheet1.Cells(i, 2).Value = "Green or Red or Blue"
    Else
      Sheet1.Cells(i, 2).Value = "XXX"
    End If
  Next i
End Sub

Here is the result

Excel Workbook
	A	B
1	Green	XXX
2	Orange	XXX
3	Red	XXX
Sheet1

 

[Mallesh23]

Hi Peter,
Thank you so much for giving multiple option, I will practice on it and improve my coding.


Regards,
mg

 

[mohadin]

Ooops
I must have tested in a wrong way
Terribly sorry
And thank you Mr. peter for letting me know my fault