vba IF Like with spaces

danuk1976

Board Regular
Joined
Nov 17, 2013
Messages
56
Hi all

Does anybody know the symbol for space when doing a like or am I missing something

this does not work
Code:
If Me.TextBox5.Text Like "*At At*" Then        
Me.TextBox5.ForeColor = vbRed
        Else
        Me.TextBox5.ForeColor = vbBlack
    End If

I also have this below looking for a match with ' character (eg brian's)

it works good, so need the character code for a space
Code:
If Me.TextBox5.Text Like "*ƒ??*" Then       
Me.TextBox5.ForeColor = vbRed
        Else
        Me.TextBox5.ForeColor = vbBlack
    End If
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,028
Office Version
2010
Platform
Windows
Does anybody know the symbol for space when doing a like or am I missing something

this does not work
Code:
If Me.TextBox5.Text Like "*At At*" Then        
...
...
There is not special character for a space... a space is a space. As written, your If..Then line of code above is checking TextBox5 for any number of unspecified characters followed by an uppercase "A" followed by a lowercase "t" followed by a (normal) space character followed by an uppercase "A" followed by a lowercase "t" followed by any number of unspecified characters. Notice the uppercase and lowercase as that is important... if either of those A's are lowercase, or either of those t's are uppercase, the test will fail. Also, if TextBox5 contains two spaces between the two "At" text, the test will also fail.



I also have this below looking for a match with ' character (eg brian's)

it works good, so need the character code for a space
Code:
If Me.TextBox5.Text Like "*ƒ??*" Then       
...
...
Just pointing out that the above If..Then test is not testing for an apostrophe (') at all... those question marks will allow any characters to exist at the two characters after the lowercase "f". If you want to test for an apostrophe directly, then put an apostrophe in at that location.
 

danuk1976

Board Regular
Joined
Nov 17, 2013
Messages
56
thank you kind sir, running this vba in publisher but don't think that should make a difference

I will try this again over the weekend and give some feedback on where I am going wrong
 

danuk1976

Board Regular
Joined
Nov 17, 2013
Messages
56
okay schoolboy error, i was running 2 instances as below so yes "At At" was coloured red but if "As As" was false it quickly coloured black

Code:
[COLOR=#574123]If Me.TextBox5.Text Like "*At At*" Then        
[/COLOR]Me.TextBox5.ForeColor = vbRed
Else
Me.TextBox5.ForeColor = vbBlack [COLOR=#574123]    
End If[/COLOR]

[COLOR=#574123]If Me.TextBox5.Text Like "*As As*" Then        
[/COLOR]Me.TextBox5.ForeColor = vbRed
Else
Me.TextBox5.ForeColor = vbBlack 
[FONT=Verdana]End If[/FONT]  [COLOR=#574123]
[/COLOR]
thanks again for confirmation rick
 

Watch MrExcel Video

Forum statistics

Threads
1,101,905
Messages
5,483,653
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top