On Mon, 10 Apr 2006 14:53:51 -0500, jay
wrote:


Hi there (maybe Ron)

In an effort to loop through my cells containing the date values, I
have constructed a 'while' loop, whereby "Temp" is a counter variable.

Your expression of:

Set rg = [A31] <-- this works perfectly for an individual cell !
:-)

however, my coding for the 'while' loop causes and error. Here is my
code:

Dim Cell_Item 'assumes a variant
Dim Temp As Integer
Temp = 4 'ASSUMES FIRST ROW OF COMMENTS IS IN ROW 4


Const Regex As String = "(\d{1,2}/){2}\d{4}"

While Temp < LastRow + 1
Set Cell_Item = Range("Q" & Temp).Cells


Set rg = [Cell_Item] <-- I get a compiler error here

NOTE: My first effort was to do as follows:

Set rg = ("Q" & Temp) <- also produces an error


There's some other problem in code that you have not posted.

Here's an example that works and includes code syntax similar to what you've
posted, except changed to reference A1:A13. I only included the first Sub and
not the RE...functions:

====================================
Sub GetLastDates()
Dim rg As Range
Dim LastDate As Date
Dim DaysSinceLastDate As Long

'pattern to detect a string that looks like a date
'in this case defined as 1 or 2 digits followed by
'a slash; repeated twice; and followed by four digits
'if necessary, it could be made more specific to ensure
'only valid dates if there is a chance that non-valid date
'sequences could be confused.

Const Regex As String = "(\d{1,2}/){2}\d{4}"

Dim Cell_Item 'assumes a variant
Dim Temp As Integer
Temp = 1 'ASSUMES FIRST ROW OF COMMENTS IS IN ROW 4
Const LastRow As Integer = 13

While Temp < LastRow + 1
Set Cell_Item = Range("A" & Temp).Cells
Set rg = [Cell_Item]

LastDate = REMid(rg.Text, Regex, RECount(rg.Text, Regex))
DaysSinceLastDate = Date - LastDate

Debug.Print "Last Date: " & LastDate & " is " & DaysSinceLastDate & " days ago"

Temp = Temp + 1
Wend

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


--ron