View Full Version : [SOLVED:] Searching for multiple texts within a cell (InStr)
CJW_14
04-30-2021, 12:38 AM
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
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
04-30-2021, 03:49 AM
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
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
04-30-2021, 03:02 PM
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...
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
04-30-2021, 03:40 PM
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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.