Replace cell formula with revised text as noted within a cell valu

[Eric_G]

I am attempting to replace the text contained in a cell formula with text
specified in the another cell.

For example, the formulae contained in Cells D & row referenced in cell
A700) which currently reads "Feb-08" and I wish to replace the portion of the
formula containing "Feb-08" with the text contained in Cell B & row
referenced by Cell
BXXX) where cell A700 contains a row number.

Here is my VBA code:

.Range("D" & .Range("A700") & ":AZ" & .Range("A700")).Select
Selection.Replace What:="Dec-08", Replacement:= .Range("B" &
..Range("A700"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

In summary, where the value of Cell A700 is 650, I wish to replace the
values "Dec-08" in the formula of row 650 with the value contained in Cell
B650 (which currently reads "Mar-09".

 

[Dave Peterson]

What do you have in those cells?

Is it the text "Dec-08" or a date (12/1/2008 to 12/31/2008) formatted to display
as "Dec-08"?

I'm guessing it's real dates and if that's right, then there aren't any Dec-08's
to find and replace.

But I could be wrong. Is that the line in your code that failed?

 

[Eric_G]

Hi Dave,

Thanks for your prompt response.

Actually, the cell formula is as follows:

=+VLOOKUP(D$2,'T:\mktg\STAR Keystone Reporting\Keystone\2008-Dec\[ROR Series
ATC and Models Report-Dec-08.xls]All Data'!$1:$65536,3,FALSE)

I'm simply attempting to replace the directory and file reference with
another month's file. When I do a "search and replace" without using a
macro, it works perfectly. I just have to find the right way to document the
macro to look at the proper cell value in order to correctly specify the
right replacement value in VBA terms.

 

[Dave Peterson]

.Range("D" & .Range("A700").Value & ":AZ" & .Range("A700").Value).Select
Selection.Replace What:="Dec-08", _
Replacement:=.Range("B" & .Range("A700").Value), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

Or dropping the select's:

.Range("D" & .Range("A700").Value & ":AZ" & .Range("A700").Value) _
.Replace What:="Dec-08", _
Replacement:=.Range("B" & .Range("A700").Value), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False


ps. You don't need that + after the =
=vlookup(...
is sufficient

Hi Dave,

Thanks for your prompt response.

Actually, the cell formula is as follows:

=+VLOOKUP(D$2,'T:\mktg\STAR Keystone Reporting\Keystone\2008-Dec\[ROR Series
ATC and Models Report-Dec-08.xls]All Data'!$1:$65536,3,FALSE)

I'm simply attempting to replace the directory and file reference with
another month's file. When I do a "search and replace" without using a
macro, it works perfectly. I just have to find the right way to document the
macro to look at the proper cell value in order to correctly specify the
right replacement value in VBA terms.

What do you have in those cells?

Is it the text "Dec-08" or a date (12/1/2008 to 12/31/2008) formatted to display
as "Dec-08"?

I'm guessing it's real dates and if that's right, then there aren't any Dec-08's
to find and replace.

But I could be wrong. Is that the line in your code that failed?