I have an auto generated workbook that I am pulling reports from. In
the very first row I have the following string:
///CST Period Variance Report - Cement (Initial Date: 1/14/2009
Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated
Wells)\\\

What I would like to do is pull the first date after initial date and
find that date on another workbook.
I can't even think of how to start. Please help.

Bryan

Try this code. I could of looked for the colon instead of Initial date but
incase there was more than one colon I did a little extra checking.

MyStr = "///CST Period Variance Report - Cement (Initial Date: 1/14/2009 " & _
"Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated" & _
"Wells)\\\"

StartPos = InStr(MyStr, "Initial Date:")
MyStr = Mid(MyStr, StartPos)
StartPos = InStr(MyStr, ":")
MyStr = Trim(Mid(MyStr, StartPos + 1))
EndPos = InStr(MyStr, " ")
MyDate = DateValue(Trim(Left(MyStr, EndPos - 1)))

"bpotter" wrote:

I have an auto generated workbook that I am pulling reports from. In
the very first row I have the following string:
///CST Period Variance Report - Cement (Initial Date: 1/14/2009
Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated
Wells)\\\

What I would like to do is pull the first date after initial date and
find that date on another workbook.
I can't even think of how to start. Please help.

Bryan

Can I change the Mystr equal to cell "a1"?

It is giving me a compile error. Array expected.

I changed only My
Str to equal Range("A1")

'These lines are commented
'MyStr = "///CST Period Variance Report - Cement (Initial Date: 1/14/2009 "
& _
' "Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated" & _
' "Wells)\\\"

MyStr = Range("A1")
StartPos = InStr(MyStr, "Initial Date:")
MyStr = Mid(MyStr, StartPos)
StartPos = InStr(MyStr, ":")
MyStr = Trim(Mid(MyStr, StartPos + 1))
EndPos = InStr(MyStr, " ")
MyDate = DateValue(Trim(Left(MyStr, EndPos - 1)))


"bpotter" wrote:

It is giving me a compile error. Array expected.

What is giving you a compile error? It is always a good idea to show the
formula or code that is giving you an error so we can see exactly what you
did.

As for you first question (in your other follow up post), you would do this
to get Mystr equal to what is in A1...

Mystr = Range("A1").Value

and use this line of code in place of where Joel first assigns the text
string to the variable.

Oh, and I had a follow up question for you... the text you showed in the
cell... is there any manual linefeeds in there (as my news reader shows) or
is that a single line of text?

--
Rick (MVP - Excel)


"bpotter" wrote in message
...
It is giving me a compile error. Array expected.

No manual linefeeds.
Gary's solution worked great for me.

Thank yuo so much for all the help guys.

Consider:

Function getdate(r As Range, which As Integer) As Date
s = Split(r.Value, " ")
i = 1
For j = 0 To UBound(s)
If IsDate(s(j)) Then
If i = which Then
getdate = s(j)
Exit Function
Else
i = i + 1
End If
End If
Next
End Function

So that if A1 contains:

Now is the time 1/24/2009 for all 12/25/1945 men to

then
=getdate(A1,1) will return 1/24/2009
and
=getdate(A1,2) will return 12/25/1945
--
Gary''s Student - gsnu2007k


"bpotter" wrote:

I have an auto generated workbook that I am pulling reports from. In
the very first row I have the following string:
///CST Period Variance Report - Cement (Initial Date: 1/14/2009
Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated
Wells)\\\

What I would like to do is pull the first date after initial date and
find that date on another workbook.
I can't even think of how to start. Please help.

Bryan

With the getdate function where does it store the date so I can find
it in another workbook?

I generate a report every morning with today and yesterday's date. I
then sort the main sheet to several routes and do calculations for
downtime. I then copy all downtime to a corresponding workbook the has
the months downtime in it.

What I would like to happen though is to be able to make it backwords
compatible in case I miss a day and generate a report for last week it
will find the right date in the other workbook and copy my downtime on
the correct sheet.



Bryan

With the getdate function where does it store the date so I can
find it in another workbook?

It is a function... you can assign its output to your variable...

Mystr = getdate(Range("A1"), 1)

--
Rick (MVP - Excel)

Here is a completely different approach (although if I had to guess, I'd say
your approach may be slightly efficient)...

Function GetDate(ByVal S As String, Which As Long) As Date
Words = Split(S)
For X = 0 To UBound(Words)
If Not IsDate(Words(X)) Then Words(X) = ""
Next
GetDate = Split(WorksheetFunction.Trim(Join(Words)))(Which - 1)
End Function

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Consider:

Function getdate(r As Range, which As Integer) As Date
s = Split(r.Value, " ")
i = 1
For j = 0 To UBound(s)
If IsDate(s(j)) Then
If i = which Then
getdate = s(j)
Exit Function
Else
i = i + 1
End If
End If
Next
End Function

So that if A1 contains:

Now is the time 1/24/2009 for all 12/25/1945 men to

then
=getdate(A1,1) will return 1/24/2009
and
=getdate(A1,2) will return 12/25/1945
--
Gary''s Student - gsnu2007k


"bpotter" wrote:

I have an auto generated workbook that I am pulling reports from. In
the very first row I have the following string:
///CST Period Variance Report - Cement (Initial Date: 1/14/2009
Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated
Wells)\\\

What I would like to do is pull the first date after initial date and
find that date on another workbook.
I can't even think of how to start. Please help.

Bryan

This is basically your routine, but structured around a For Each loop...

Function GetDate(ByVal S As String, Which As Long) As Date
Words = Split(S)
For Each W In Words
If IsDate(W) Then
X = X + 1
If X = Which Then
GetDate = CDate(W)
Exit For
End If
End If
Next
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is a completely different approach (although if I had to guess, I'd
say your approach may be slightly efficient)...

Function GetDate(ByVal S As String, Which As Long) As Date
Words = Split(S)
For X = 0 To UBound(Words)
If Not IsDate(Words(X)) Then Words(X) = ""
Next
GetDate = Split(WorksheetFunction.Trim(Join(Words)))(Which - 1)
End Function

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
Consider:

Function getdate(r As Range, which As Integer) As Date
s = Split(r.Value, " ")
i = 1
For j = 0 To UBound(s)
If IsDate(s(j)) Then
If i = which Then
getdate = s(j)
Exit Function
Else
i = i + 1
End If
End If
Next
End Function

So that if A1 contains:

Now is the time 1/24/2009 for all 12/25/1945 men to

then
=getdate(A1,1) will return 1/24/2009
and
=getdate(A1,2) will return 12/25/1945
--
Gary''s Student - gsnu2007k


"bpotter" wrote:

I have an auto generated workbook that I am pulling reports from. In
the very first row I have the following string:
///CST Period Variance Report - Cement (Initial Date: 1/14/2009
Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated
Wells)\\\

What I would like to do is pull the first date after initial date and
find that date on another workbook.
I can't even think of how to start. Please help.

Bryan

Thanks Rick

Both suggestions are interesting alternatives.
--
Gary''s Student - gsnu200827


"Rick Rothstein" wrote:

This is basically your routine, but structured around a For Each loop...

Function GetDate(ByVal S As String, Which As Long) As Date
Words = Split(S)
For Each W In Words
If IsDate(W) Then
X = X + 1
If X = Which Then
GetDate = CDate(W)
Exit For
End If
End If
Next
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is a completely different approach (although if I had to guess, I'd
say your approach may be slightly efficient)...

Function GetDate(ByVal S As String, Which As Long) As Date
Words = Split(S)
For X = 0 To UBound(Words)
If Not IsDate(Words(X)) Then Words(X) = ""
Next
GetDate = Split(WorksheetFunction.Trim(Join(Words)))(Which - 1)
End Function

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
Consider:

Function getdate(r As Range, which As Integer) As Date
s = Split(r.Value, " ")
i = 1
For j = 0 To UBound(s)
If IsDate(s(j)) Then
If i = which Then
getdate = s(j)
Exit Function
Else
i = i + 1
End If
End If
Next
End Function

So that if A1 contains:

Now is the time 1/24/2009 for all 12/25/1945 men to

then
=getdate(A1,1) will return 1/24/2009
and
=getdate(A1,2) will return 12/25/1945
--
Gary''s Student - gsnu2007k


"bpotter" wrote:

I have an auto generated workbook that I am pulling reports from. In
the very first row I have the following string:
///CST Period Variance Report - Cement (Initial Date: 1/14/2009
Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated
Wells)\\\

What I would like to do is pull the first date after initial date and
find that date on another workbook.
I can't even think of how to start. Please help.

Bryan