Solved

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

Posted on 2014-12-03
8
419 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vlookup for in-between dates 4 41
VB Script: split files based a intlines flag 27 47
Problem to file 5 17
VBScript on Html 11 20
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

832 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