Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace cell formula with revised text as noted within a cell valu
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". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace cell formula with revised text as noted within a cell valu
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace cell formula with revised text as noted within a cell
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace cell formula with revised text as noted within a cell
.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 Eric_G wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add If formula in macro (replace cell text value with numeric valu | Excel Discussion (Misc queries) | |||
Can a cell defined by a formula become an exportable numeric valu. | Excel Worksheet Functions | |||
How do I replace text within a formula with a diff cell value? | Excel Worksheet Functions | |||
can conditional formatting on one cell value to another cell valu | Excel Worksheet Functions | |||
I want to chage a whole rows text color based on single cell valu. | Excel Worksheet Functions |