Use (text string & wildcards) with Instr?

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
Tue Jan 27 07:55:17 CST 2004

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
"Ed" <> wrote in message news:%23iJr8qN5DHA.1368@TK2MSFTNGP10.phx.gbl...
>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
>
>

anonymous
Tue Jan 27 08:01:06 CST 2004

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
Tue Jan 27 08:33:22 CST 2004

Thanks for the help, Dave.

Ed

"Dave Lett" <anonymous@discussions.microsoft.com> wrote in message
news:DEA80E66-7950-47A5-BE8E-DE60300FC82E@microsoft.com...
> 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
Tue Jan 27 08:43:09 CST 2004

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"
<dkr@mOSTvALUABLEpROFESSIONALs.org> wrote in message
news:u5e8y0N5DHA.1936@TK2MSFTNGP12.phx.gbl...
> 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
> "Ed" <> wrote in message news:%23iJr8qN5DHA.1368@TK2MSFTNGP10.phx.gbl...
> >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
Tue Jan 27 09:27:46 CST 2004

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


"Ed" <ed_millis@removethis.hotmail.com> schrieb im Newsbeitrag
news:%23iJr8qN5DHA.1368@TK2MSFTNGP10.phx.gbl...
> 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
>
>

anonymous
Tue Jan 27 09:41:04 CST 2004

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
Tue Jan 27 10:43:31 CST 2004

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

"Dave Lett" <anonymous@discussions.microsoft.com> wrote in message
news:39493FB0-3F5A-4528-B3F0-D7C950365B34@microsoft.com...
> 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

anonymous
Tue Jan 27 11:36:05 CST 2004

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
Tue Jan 27 15:45:02 CST 2004

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

"Dave Lett" <anonymous@discussions.microsoft.com> wrote in message
news:A59C3CD6-4A35-4905-9F95-154210B4F7D8@microsoft.com...
> 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