vba IF Like with spaces
Results 1 to 4 of 4

Thread: vba IF Like with spaces

  1. #1
    Board Regular
    Join Date
    Nov 2013
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba IF Like with spaces

    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

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,120
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: vba IF Like with spaces

    Quote Originally Posted by danuk1976 View Post
    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.



    Quote Originally Posted by danuk1976 View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Nov 2013
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba IF Like with spaces

    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

  4. #4
    Board Regular
    Join Date
    Nov 2013
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba IF Like with spaces

    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:
    If Me.TextBox5.Text Like "*At At*" Then        
    Me.TextBox5.ForeColor = vbRed
    Else
    Me.TextBox5.ForeColor = vbBlack     
    End If
    
    If Me.TextBox5.Text Like "*As As*" Then        
    Me.TextBox5.ForeColor = vbRed
    Else
    Me.TextBox5.ForeColor = vbBlack 
    End If  
    
    thanks again for confirmation rick

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •