ExcelBanter - View Single Post

Ron Rosenfeld

On 22 Sep 2006 14:01:37 -0700, wrote:

One more thing I forgot to add. I will email this worksheet to people
who won't have Longre's free morefunc.xll add-in

That in itself is not an issue. With the morefunc add-in comes a Menu option
to embed it within the worksheet. This enables the worksheet to be emailed
with the add-in installed and functioning.

A small modification to my previous routine will check for a valid entry given
your new rules. The TRUE or FALSE is printed in the immediate window as I'm
not sure what else you want to do with the results:

===============================
Option Explicit

Sub Validate()
Dim c As Range
Dim strDate As String
Dim strF As String
Dim i As Long
Dim ValidEntry As Boolean
Dim sTemp As String

Const PatternDate As String = "^\d{4}-\d{2}-\d{2}\b"
Const PatternF As String = "(" & PatternDate & _
")?" & "((^|\s)(F\d{1,2}(,|$))*)?"
'Not good form to make both parts of the regex _
optional, but it'll work because of other code below

For Each c In Selection
strDate = Run([regex.mid], c.Text, PatternDate)
ValidEntry = IsDate(strDate) Or Left(c.Text, 1) Like "[Ff]"

If ValidEntry = True Then
strF = Run([regex.mid], c.Text, PatternF, , False)
ValidEntry = (strF = c.Text)
End If
Debug.Print c.Text & " " & ValidEntry
Next c

End Sub
=====================================

--ron