Solved

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

Posted on 2014-12-03
8
432 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

856 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