I have looked for this capability but haven't found exactly what I'm looking
for yet...

Within VBA on a macro I have used a "cells.replace" with great success when
locating an "old" date and replacing it with a "new" date.

strOldDate = Format(Now() - (Weekday(Now())) - 7, "mmmm d, yyyy")
strEffectiveDate = Format(Now() - Weekday(Now()), "mmmm d, yyyy")
Sheets("Executive Summary").Select
Cells.Replace What:=strOldDate, Replacement:=strEffectiveDate, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True

Now I would like to find on the worksheet a cell with a date value in it and
collect it as the "strOldDate".
1) Is it possible to do it similar to the Cells.Replace? Or
2) If I know the exact cell I want to search (e.g.: A3)?

Thank you in advance.

--

MJ

The what and replacement string can be cell references

What:=sheets("Sheet1").Range("A1"), Replacement:=sheets("Sheet1").Range("A2")

"MJ" wrote:

I have looked for this capability but haven't found exactly what I'm looking
for yet...

Within VBA on a macro I have used a "cells.replace" with great success when
locating an "old" date and replacing it with a "new" date.

strOldDate = Format(Now() - (Weekday(Now())) - 7, "mmmm d, yyyy")
strEffectiveDate = Format(Now() - Weekday(Now()), "mmmm d, yyyy")
Sheets("Executive Summary").Select
Cells.Replace What:=strOldDate, Replacement:=strEffectiveDate, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True

Now I would like to find on the worksheet a cell with a date value in it and
collect it as the "strOldDate".
1) Is it possible to do it similar to the Cells.Replace? Or
2) If I know the exact cell I want to search (e.g.: A3)?

Thank you in advance.

--

MJ

Joel, perhaps I did not make myself clear... I wish to collect a date from a
string located in a cell to use to increment the effective date.

After I increment that data, then I would use the replace code as previously
written, unless there is a better idea out there.

Thanks again
--

MJ


"Joel" wrote:

The what and replacement string can be cell references

What:=sheets("Sheet1").Range("A1"), Replacement:=sheets("Sheet1").Range("A2")

"MJ" wrote:

I have looked for this capability but haven't found exactly what I'm looking
for yet...

Within VBA on a macro I have used a "cells.replace" with great success when
locating an "old" date and replacing it with a "new" date.

strOldDate = Format(Now() - (Weekday(Now())) - 7, "mmmm d, yyyy")
strEffectiveDate = Format(Now() - Weekday(Now()), "mmmm d, yyyy")
Sheets("Executive Summary").Select
Cells.Replace What:=strOldDate, Replacement:=strEffectiveDate, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True

Now I would like to find on the worksheet a cell with a date value in it and
collect it as the "strOldDate".
1) Is it possible to do it similar to the Cells.Replace? Or
2) If I know the exact cell I want to search (e.g.: A3)?

Thank you in advance.

--

MJ