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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,059
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,102,872
Messages
5,489,427
Members
407,687
Latest member
NeoSez

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top