Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to modify vba code to search cell for text with wildcards?

Posted on 2014-12-03
8
Medium Priority
?
479 Views
Last Modified: 2014-12-03
The below code will search cell A1 for the text “wip” and “PC” and highlight the text, but I also need it to find all dates.  I need to search text for a date equal to this format ##/##/##.

Example of data:
02/02/13 14:00:21 (john.doe):
09/21/14 09:00:21 (jane.doe):
12/14/15 16:00:21 (john.doe):

Sub HighLightText()
Dim intStart As Integer
Dim intEnd As Integer
intStart = 1
Do
    intStart = InStr(intStart, LCase(Range("A1").Value), "wip")
    If intStart > 0 Then
        Range("A1").Characters(intStart, 3).Font.Color = vbBlue
        intStart = intStart + 1
    End If
Loop Until intStart = 0

intStart = 1
Do
    intStart = InStr(intStart, LCase(Range("A1").Value), "PC")
    If intStart > 0 Then
        Range("A1").Characters(intStart, 2).Font.Color = vbRed
        intStart = intStart + 1
    End If
Loop Until intStart = 0

End Sub

Open in new window

0
Comment
Question by:kbay808
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478009
Then we have another loop:

intStart = 1
for intStart = 1 to len(cells(1,1))
    if mid(cells(1,1),intStart,8) like "##:##:##" then
        Range("A1").Characters(intStart, 8).Font.Color = vbRed
        intStart = intStart + 7
    End If
Next

Open in new window

0
 

Author Comment

by:kbay808
ID: 40478047
Is this how it supposed to look?  It's not highlighting any of the dates.  Is the value supposed to be "##:##:##"?  The date has "/".

Sub HighLightText()
Dim intStart As Integer
Dim intEnd As Integer
intStart = 1
Do
    intStart = InStr(intStart, LCase(Range("A3").Value), "wip")
    If intStart > 0 Then
        Range("A3").Characters(intStart, 3).Font.Color = vbBlue
        intStart = intStart + 1
    End If
Loop Until intStart = 0

intStart = 1
For intStart = 1 To Len(Cells(1, 1))
    If Mid(Cells(1, 1), intStart, 8) Like "##:##:##" Then
        Range("A3").Characters(intStart, 8).Font.Color = vbRed
        intStart = intStart + 7
    End If
Next

intStart = 1
Do
    intStart = InStr(intStart, LCase(Range("A3").Value), "PC")
    If intStart > 0 Then
        Range("A3").Characters(intStart, 2).Font.Color = vbRed
        intStart = intStart + 1
    End If
Loop Until intStart = 0

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478050
Yep - change it to ##/##/##

intStart = 1
For intStart = 1 To Len(Cells(1, 1))
    If Mid(Cells(1, 1), intStart, 8) Like "##/##/##" Then
        Range("A3").Characters(intStart, 8).Font.Color = vbRed
        intStart = intStart + 7
    End If
Next
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kbay808
ID: 40478079
It did not work.  I even tried it in a new workbook all by it self, but no joy.  

Sub HighLightText()
Dim intStart As Integer
Dim intEnd As Integer
    
intStart = 1
 For intStart = 1 To Len(Cells(1, 1))
     If Mid(Cells(1, 1), intStart, 8) Like "##/##/##" Then
         Range("A3").Characters(intStart, 8).Font.Color = vbRed
         intStart = intStart + 7
     End If
Next
End Sub

Open in new window

0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40478087
Probably because we are both searching cell A1 (lines 6 and 7), but highlighting cell A3 (line 8).

And you don't need lines 3 or 5.

Updated code, using cell A3.

Sub HighLightText()
Dim intStart As Integer
    
 For intStart = 1 To Len(Cells(3, 1))
     If Mid(Cells(3, 1), intStart, 8) Like "##/##/##" Then
         Range("A3").Characters(intStart, 8).Font.Color = vbRed
         intStart = intStart + 7
     End If
Next
End Sub

Open in new window

0
 

Author Comment

by:kbay808
ID: 40478097
The data is in cell A3 and I removed lines 3 and 5.  Still no joy.  I uploaded the example file so you can see what I'm looking at.
Example.xlsm
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478101
Update the code to the one in my previous post (which is more than removing lines 3 and 5), and it will work.
0
 

Author Closing Comment

by:kbay808
ID: 40478118
It works GREAT!!!   Thank you very much.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question