Solved

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

Posted on 2014-12-03
8
404 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now