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.
You can also use
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(FIND(""-"",RC[-1])),""Blue"",""Red"")"
How to use IF function in Excel: examples for text, numbers, dates , You can use a formula based on the LEFT function and the FIND function to extract text before dash character in a given cell in Excel. Like this: Fill blank cells with dashes by using VBA code. Fill blank cells with dashes by using Kutools for Excel. Fill blank cells with dash or any other specific values: Kutools for Excel’s Fill Blank Cells feature can help you to fill blank cells with value above, with liner values, with specific values as you need.
Or a one-liner:
ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND(""-"",RC[-1])),""Red"",""Blue"")"
How to Get Text before or after Dash Character in Excel, Excel VBA IF THEN Statement is one of the most useful statements in VBA. We also take a look at looping through a range of cells using Excel table referencing. Highlight the reference in the formula bar (do not include the equal's sign) Format of the VBA If-Then Statement. The format of the If Then statement is as follows. If [condition is true] Then. The If keyword is followed by a Condition and the keyword Then Every time you use an If Then statement you must use a matching End If statement. When the condition evaluates to true, all the lines between If Then and End If are processed.
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
Excel VBA IF THEN Statement (and a useful tip) - Xelplus, How to perform logical tests with IF statements using the Excel IF function. take the Total $k in cell F2 x 10%, but if it's not > 5 then take the Total $k in cell F2 x 5%) and have Excel reformat it in the same cell (without VBA). hyphens typed in at the same time, or use a formula in another cell to convert VBA - If statement to identify if first character of a value is a number or letter
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.
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.
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.
How to write Excel IF Function Statements, EXACT, Checks to see if two text values are identical (case sensitive), = Functions: To use an Excel function in VBA prefix with Application. change the “Allow” dropdown to Custom, then, assuming the active cell is A1, enter =Exact(A1,UPPER(A1)) The string uses a hyphen (-) as the separator in the following example:. IF statement in VBA is sometimes also called as IF THEN ELSE Statement. The task of IF Statement is to check if a particular condition is met or not. The task of IF Statement is to check if a particular condition is met or not.
The error, corrected (but not pointed out) in several of the other answers lies in changing your formula from
" - "
"" - ""
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
Strings and Manipulations, howto-sum-alternative-rows-columns-excel-using-sumif There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Im trying to create a function that can highlight various amounts related to Excel Unable to remove Em dash and replace with En dash. Anyone know an excel formula or a VBA code for excel to replace Em dash with En dash. I just cant figure it out. My VLookup formula is not working for cell A1 which contains value 'WARSAW - ICSD RED – EB' as there is a long dash in text.
Learn Excel IF and Then Formula, If a border exists the value “True” will be printed in the adjacent cell in column B. If a border doesn't exist Excel VBA, Borders In Column A, Result Private Function Get_LineStyle(ByVal intCode _ xlSlantDashDot Then VBA Code to Identify if a row has any colored cells in it Hi All, I have been writing a macro whereby any cells with incorrect information in have been highlighted in the color yellow to be identifiable to those who have to correct them.
Excel VBA, Get Border Properties, If value in range then return value - LOOKUP function; If value in The picture above shows data in cell range B3:C5, the search value is You can also do lookups in date ranges, dates in Excel are actually If you add one dash (-) in front of this logical function the result is the Put your VBA code here. To check if a cell is empty with VBA, use a macro with the following statement structure: If IsEmpty (Cell) Then. StatementsIfCellIsEmpty. StatementsIfCellIsNotEmpty. Process Followed by VBA Code to Check if Cell is Empty. VBA Statement Explanation. Line #1: If IsEmpty (Cell) Then. Item: If… Then. VBA Construct: Opening statement of If
How to return a value if lookup value is in a range, xlsx format). Example 2 – Highlight Cells with Negative Values. Suppose that you have a column Using the IF function in Excel - formula examples. IF examples for numbers; How to use the IF function with text values; Using Excel IF function with dates; Excel IF statement for blank, non-blank cells; Excel IF function - syntax and usage. The IF function is one of Excel's logical functions that evaluates a certain condition and returns the value you specify if the condition is TRUE, and another value if the condition is FALSE. The syntax for Excel IF is as follows:
- To create an Excel formula that will do this, suggest you use the
FINDfunction. In VBA you could use