Searching for multiple texts within a cell (InStr)

[CJW_14]

Hi All,

What am I doing wrong here?

The IF statement works but I cant get the elseif statement to work where I want to check for multiple words within the cell?

Many thanks :)


Option Compare Text
Sub Test()

Dim lastrow As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

If InStr(Range("A" & i).Value, "Red") > 0 Then
Range("C" & i).FormulaR1C1 = "Correct"

ElseIf InStr(Range("A" & i).Value, "Blue") > 0 And InStr(Range("A" & i).Value, "Green") > 0 Then
Range("C" & i).Value = "Correct"

Else

'xxxxxx

End If

Next

End Sub

[snb]

In VBA:


Sub M_snb()
sn = cells(1).currentregion

for j = 1 to ubound(sn)
if instr(sn(j,1),"Red") then sn(j,3) = "Red"
if instr(sn(j,1),"Blue") and if instr(sn(j,1),"Green") then sn(j,3) = "Blue_Green"
Next

cells(1).currentregion = sn
End Sub

[p45cal]

Looking at your sheet you have stuff in column B, is that line meant to be:
ElseIf InStr(Range("A" & i).Value, "Blue") > 0 And InStr(Range("B" & i).Value, "Green") > 0 Then

[SamT]

I think P45cal is correct. That would make snb's and if instr(sn(j,1),"Green") should be and if instr(sn(j,2),"Green"). Do not use Option Explicit with snb's code.

Another example is:
Option Explicit
Option Compare Text 'For case insensitivity of color names in cells

Sub Test()
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If CBool(InStr(Cells(i, "A"), "Red")) Then
Cells(i, "C") = "Correct"
ElseIf CBool(InStr(Cells(i, "A"), "Blue") * InStr(Cells(i, "B").Offset(, 1), "Green")) Then
Cells(i, "C") = "Correct"
Else
Cells(i, "C") = "Incorrect"
End If
Next
End Sub

The compiler is supposed to auto convert all parameters after an If to Booleans, but I like to make sure. ie, The CBool() is supposed to be redundant. Zero = False, all other numbers = True. The Multiplier Operator(*) is the Math equivalent of Boolean "And". (+) is the equivalent of "Or".

[CJW_14]

Hi Guys,

Sorry late reply. I realised I made a critical error in my mockup and should have explained myself better to solve the confusion, sorry. What I should have had was this:

28387

So im looking within each cell, for multiple values, if both of those value exist then do something...

[SamT]

I don't see why your original code doesn't work

snb's original code should work perfectly

And if you need help to modify his or my code, you need more help than a Forum can offer

[CJW_14]

Thanks SamT, I thought my original code would have worked so not sure why.

I actually got your code working with the modification to look at the same cell.

Many thanks :)

[SamT]

:thumb