Clash Royale CLAN TAG#URR8PPP

Excel VBA - if/then statement - Identifying cells with a dash

I am trying to write a VBA line where if cell A1 contains a dash anywhere in the cell, then B1 will say "Blue". If there is no dash, then B1 would say "Red".

I have the following code written, but it's not working and I'm not sure if I'm doing the "like" part correctly:

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=" - ",""Blue"",""Red"")"


Thank you for any help you can provide! I've done so much searching, but have been unable to find any examples that didn't include specific numbers or text.

FIND

Like

InStr

5 Answers
5

You can also use

ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(FIND(""-"",RC[-1])),""Blue"",""Red"")"


Or a one-liner:

ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND(""-"",RC[-1])),""Red"",""Blue"")"


You could do it with a simple VBA script like this:

Sub Test()
Dim sh1 As Worksheet
Set sh1 = Sheets("Sheet1")

Application.ScreenUpdating = False

For x = 1 To sh1.Cells(rows.Count, "A").End(xlUp).Row

If InStr(1, sh1.Range("A" & x).Value, UCase("-"), 1) > 0 Then sh1.Range("B" & x).Value= "Red"
If InStr(1, sh1.Range("A" & x).Value, UCase("-"), 1) < 0 Then sh1.Range("B" & x).Value = "Blue"

Next x

Application.ScreenUpdating = True

End Sub


I don't believe that put a formula in every "B" column cells it's a good pratice, Excel can take a long time to calculate.

Try this:

Sub Example()

mySheet.Cells(1, "B").Value = IIf(Not InStr(1, mySheet.Cells(1, "A"), Chr(45), vbTextCompare) = 0, "Blue", "Red")

End Sub


You can loop through every cell you want to put that condition using this code.

Functions:

IIf is equals to Excel Worksheet Function "IF".

InStr search a string in another string, you can pass a unique char as criterea. I used chr(45) because it returns a char according to the passed code, 45 references to Dash code.

The error, corrected (but not pointed out) in several of the other answers lies in changing your formula from

" - "

" - "

to

"" - ""

"" - ""

i.e. going from single double-quotes around your - to double double-quotes. The single quote is ending your string - you can even see that the - shows up in black text in your question instead of red text, therefore, it's not part of the string being inserted into ActiveCell.FormulaR1C1.

-

-

ActiveCell.FormulaR1C1

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.