Search and Collect Date from Cell Containing a String with a Date

[MJ]

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.

 

[Joel]

The what and replacement string can be cell references

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

 

[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

 

[Joel]

Dates are store in excel as numbers with Jan 1, 1900 = 1. You need to search
or replace for the number. To get the number use DateValue("1/30/09") or
other date conversion functions like DateSerial(2009, 1, 30). Jan 30, 2009 =
39843. Depending how the cell is formated determines how it gets displayed.
If a cell is in general format excel will try to match the data enetered a
set of predetermined formats. If you have a cell formated as a date and you
enter 39843 it will display Jan 30, 2009.