Use (text string & wildcards) with Instr?

[Ed]

I need to find if the text in certain table cells contain report numbers.
All have the format "D4-F" followed by 6 digits. I tried coding
If Instr(1, strText, ("D4-F" & [0-9]{6}), vbTextCompare)
but the compiler didn't like the wildcards. Is it possible to do this?

Ed

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Ed,

[0-9]{6} only works with a wildcard Find. Try something like

Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="D4-F[0-9]{6}", MatchWildcards:=True,
Wrap:=wdFindStop, Forward:=True) = True
If Selection.Information(wdWithInTable) = True Then
'Do whatever
End If
Loop
End With


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Dave Lett]

Hi Ed

I don't think there is a VBA function that accomplishes your task; however, maybe you can select the cell and then run a find routine on that cell, as in the following paired routine
'''--------------------------------------
Public Sub Test(
Dim iRowLoop As Intege
Dim iColumnLoop As Intege
Dim oTbl As Tabl
Set oTbl = ActiveDocument.Tables(1
For iColumnLoop = 1 To oTbl.Columns.Coun
For iRowLoop = 1 To oTbl.Rows.Coun
Call fContainsReportNumber(iRowLoop, iColumnLoop
Next iRowLoo
Next iColumnLoo
End Su
'''--------------------------------------
'''--------------------------------------
Public Function fContainsReportNumber(iRow As Integer, iCol As Integer) As Boolea
fContainsReportNumber = Fals
ActiveDocument.Tables(1).Cell(iRow, iCol).Selec
With Selection.Fin
.Text = "D4-F[0-9]{6}
.MatchWildcards = Tru
If .Execute The
fContainsReportNumber = Tru
End I
End Wit
MsgBox fContainsReportNumbe
End Functio
'''--------------------------------------

HTH
Dave

 

[Ed]

Thanks for the help, Dave.

Ed

Hi Ed,

I don't think there is a VBA function that accomplishes your task;

however, maybe you can select the cell and then run a find routine on that
cell, as in the following paired routine:

'''---------------------------------------
Public Sub Test()
Dim iRowLoop As Integer
Dim iColumnLoop As Integer
Dim oTbl As Table
Set oTbl = ActiveDocument.Tables(1)
For iColumnLoop = 1 To oTbl.Columns.Count
For iRowLoop = 1 To oTbl.Rows.Count
Call fContainsReportNumber(iRowLoop, iColumnLoop)
Next iRowLoop
Next iColumnLoop
End Sub
'''---------------------------------------
'''---------------------------------------
Public Function fContainsReportNumber(iRow As Integer, iCol As Integer) As Boolean
fContainsReportNumber = False
ActiveDocument.Tables(1).Cell(iRow, iCol).Select
With Selection.Find
.Text = "D4-F[0-9]{6}"
.MatchWildcards = True
If .Execute Then
fContainsReportNumber = True
End If
End With
MsgBox fContainsReportNumber
End Function
'''---------------------------------------

HTH,
Dave

 

[Ed]

Thanks, Doug, for the help. What I'm going to wind up with is checking the
all the text contained in specific table cells to see if that cell contains
only a report number - the text will be 10 characters long and be in the
described format. I can check the character length using either
Selection.Text or by setting it into a String. Can I confine a Find to
either of those? Or do I set a range = Selection.Range, and work within
that?

Ed

"Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS"

Hi Ed,

[0-9]{6} only works with a wildcard Find. Try something like

Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="D4-F[0-9]{6}", MatchWildcards:=True,
Wrap:=wdFindStop, Forward:=True) = True
If Selection.Information(wdWithInTable) = True Then
'Do whatever
End If
Loop
End With


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

I need to find if the text in certain table cells contain report numbers.
All have the format "D4-F" followed by 6 digits. I tried coding
If Instr(1, strText, ("D4-F" & [0-9]{6}), vbTextCompare)
but the compiler didn't like the wildcards. Is it possible to do this?

Ed

 

[Martin Seelhofer]

Hi Ed

I need to find if the text in certain table cells contain report numbers.
All have the format "D4-F" followed by 6 digits. I tried coding
If Instr(1, strText, ("D4-F" & [0-9]{6}), vbTextCompare)
but the compiler didn't like the wildcards. Is it possible to do this?

If you have a recent Windows on your machine, you can write
your own InStrPat-function using the RegExp-object of VBScript.
This will look something like this: (late-binding approach, note that
this function will cause an error if the RegExp-object is not
available on your computer)

Function InStrPat(ByVal srcStr As String, ByVal pattern As String) As
Boolean
Dim re As RegExp
Set re = CreateObject("VBScript.RegExp")
' Allow arbitrary characters/numbers before and after the
' pattern. This is done using .* which represents a sequence
' of zero or more occurences of any character/digit.
re.pattern = ".*" & pattern & ".*"
InStrPat = re.test(srcStr)
End Function

And the usage for the above function:

Sub regexptest()
Dim pat As String

pat = "D4-F[0-9]{6}"

MsgBox InStrPat("D4-F000000", pat)
MsgBox InStrPat("and again D4-F100110 asd", pat)
MsgBox InStrPat(Selection.Text, pat)
End Sub


Cheers,
Martin

I need to find if the text in certain table cells contain report numbers.
All have the format "D4-F" followed by 6 digits. I tried coding
If Instr(1, strText, ("D4-F" & [0-9]{6}), vbTextCompare)
but the compiler didn't like the wildcards. Is it possible to do this?

Ed

 

[Dave Lett]

Hi Ed

I've modified the sub and function a little.
'''------------------------------------------------------------------------
Public Sub test(
MsgBox "Report only?" & vbCrLf & fReportOnly(iRow:=3, iCol:=2
End Su
'''------------------------------------------------------------------------
'''------------------------------------------------------------------------
Public Function fReportOnly(iRow As Integer, iCol As Integer) As Boolea
Dim oRng As Rang
Dim oClRng As Rang
Set oClRng = ActiveDocument.Tables(1).Cell(iRow, iCol).Rang
Set oRng = ActiveDocument.Range
(Start:=oClRng.Start,
End:=oClRng.End
''' move the end of the range by 1 character or th
''' end of cell character will be counted i
''' characters.count (the next line
oRng.MoveEnd Unit:=wdCharacter, Count:=-

''' this counts the number of characters in the cel
''' if the number is exactly ten, then test if it's a report numbe
''' if the number ISN'T exactly the, then it cannot be a report number onl
If oRng.Characters.Count = 10 The
''' select the entire cell, including the end of cell characte
''' if you select oRng, then the .Execut
''' will always return a misleading "False
oClRng.Selec
With Selection.Fin
.Text = "D4-F[0-9]{6}
.MatchWildcards = Tru
If .Execute The
''' only ten characters AND a report numbe
fReportOnly = Tru
Els
''' only ten characters BUT NOT a report numbe
fReportOnly = Fals
End I
End Wit
Els
''' not exactly ten characters; therefore, NOT a report number onl
fReportOnly = Fals
End I
End Functio
'''------------------------------------------------------------------------

HTH
Dave

 

[Ed]

Wow! Thanks, Dave. I've just started trying to use ranges, and I was
running into that very error (using the cell range). As I read your code,
oRng is only the text, while oClRng is the entire cell contents?

Also, this will be in a loop. Do I need to Set oRng (and the other ranges)
= Nothing at every loop? Or only at the end of entire Sub? Or do I need to
worry about it at all?

Ed

Hi Ed,

I've modified the sub and function a little.
'''-------------------------------------------------------------------------
Public Sub test()
MsgBox "Report only?" & vbCrLf & fReportOnly(iRow:=3, iCol:=2)
End Sub
'''-------------------------------------------------------------------------'''-------------------------------------------------------------------------
Public Function fReportOnly(iRow As Integer, iCol As Integer) As Boolean
Dim oRng As Range
Dim oClRng As Range
Set oClRng = ActiveDocument.Tables(1).Cell(iRow, iCol).Range
Set oRng = ActiveDocument.Range _
(Start:=oClRng.Start, _
End:=oClRng.End)
''' move the end of the range by 1 character or the
''' end of cell character will be counted in
''' characters.count (the next line)
oRng.MoveEnd Unit:=wdCharacter, Count:=-1

''' this counts the number of characters in the cell
''' if the number is exactly ten, then test if it's a report number
''' if the number ISN'T exactly the, then it cannot be a report number only
If oRng.Characters.Count = 10 Then
''' select the entire cell, including the end of cell character
''' if you select oRng, then the .Execute
''' will always return a misleading "False"
oClRng.Select
With Selection.Find
.Text = "D4-F[0-9]{6}"
.MatchWildcards = True
If .Execute Then
''' only ten characters AND a report number
fReportOnly = True
Else
''' only ten characters BUT NOT a report number
fReportOnly = False
End If
End With
Else
''' not exactly ten characters; therefore, NOT a report number only
fReportOnly = False
End If
End Function
'''-------------------------------------------------------------------------


HTH,
Dave

 

[Dave Lett]

Hi Ed,

Yes, oRng is range for text only and oClRng is the entire cell range.
If you put the function in a loop, then no, you don't need to set oRng and oClRng to = Nothing at every loop (they get redefined as you change the row/column; just make sure that the Set for each is in the loop). AFAIK, it is good practice to set them to =Nothing at the end of the sub.

HTH,
Dave

 

[Ed]

Dave, thank you so much! It works like a champ, and fixes the last broken
link in a chain of macros to help me with my reports. I've got reports with
multiple tables of interim report numbers broken out by classifications.
When the master Excel spreadsheet of interim reports was updated, it could
take 2-3 hours to compile a list of new numbers and slog through a report
checking them. Now, I compile a list from the spreadsheet, check that list
against the report and highlight the ones that match, reporting the ones
that didn't match. The bugaboo was to go through the report, find the
correct tables and find the interim numbers that didn't get highlighted.
Your code provided the final piece of the puzzle - now the whole process
takes less than five minutes!

I couldn't make it work as a separate function, though, so I had to take the
code and insert it directly into the loop. Caused some consternation with
Ifs and Withs, but that's just due to my inexperience (didn't know how to
call the function, pass the answer into the sub, and which has to terminate
first - the If or the With). But it's working now!

Many, many thanks!
Ed

Hi Ed,

Yes, oRng is range for text only and oClRng is the entire cell range.
If you put the function in a loop, then no, you don't need to set oRng and

oClRng to = Nothing at every loop (they get redefined as you change the
row/column; just make sure that the Set for each is in the loop). AFAIK, it
is good practice to set them to =Nothing at the end of the sub.