Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search & Replace macro
Hi,
I cant find this anywhere so please excuse possible repeat.... I would like a macro which will search for the value of a cell (in one worksheet) and simply replace a value in another worksheet with the value of another. E.G. Search for contents of B30 and replace with contents of F30. Simple i hear you say, but my current macro always contains the actual value at time of recording so if i change the value of the cells, the macro searches for and replaces the values previously stored and not the new values i'm using. Here is what i have... Sheets("Summary").Select Range("B30").Select Selection.Copy Range("F30").Select Application.CutCopyMode = False Selection.Copy Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False I would like the values (nn) and 20 to be dynamic and always refer to what's in the range("B30") and ("F30) rather than insert fixed values (taken at time of recording). Many thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search & Replace macro
WOW. nice one.
Thanks very much. "Don Guillett" wrote: try with Sheets("Summary") .Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False end with -- Don Guillett Microsoft MVP Excel SalesAid Software "Beebolbod" wrote in message ... Hi, I cant find this anywhere so please excuse possible repeat.... I would like a macro which will search for the value of a cell (in one worksheet) and simply replace a value in another worksheet with the value of another. E.G. Search for contents of B30 and replace with contents of F30. Simple i hear you say, but my current macro always contains the actual value at time of recording so if i change the value of the cells, the macro searches for and replaces the values previously stored and not the new values i'm using. Here is what i have... Sheets("Summary").Select Range("B30").Select Selection.Copy Range("F30").Select Application.CutCopyMode = False Selection.Copy Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False I would like the values (nn) and 20 to be dynamic and always refer to what's in the range("B30") and ("F30) rather than insert fixed values (taken at time of recording). Many thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search & Replace macro
How could i modify this to copy the variables from one worksheet and paste
into another please? "Beebolbod" wrote: WOW. nice one. Thanks very much. "Don Guillett" wrote: try with Sheets("Summary") .Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False end with -- Don Guillett Microsoft MVP Excel SalesAid Software "Beebolbod" wrote in message ... Hi, I cant find this anywhere so please excuse possible repeat.... I would like a macro which will search for the value of a cell (in one worksheet) and simply replace a value in another worksheet with the value of another. E.G. Search for contents of B30 and replace with contents of F30. Simple i hear you say, but my current macro always contains the actual value at time of recording so if i change the value of the cells, the macro searches for and replaces the values previously stored and not the new values i'm using. Here is what i have... Sheets("Summary").Select Range("B30").Select Selection.Copy Range("F30").Select Application.CutCopyMode = False Selection.Copy Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False I would like the values (nn) and 20 to be dynamic and always refer to what's in the range("B30") and ("F30) rather than insert fixed values (taken at time of recording). Many thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search & Replace macro
Just reference them ..Cells.Replace sheets("othersheetname").range("b30"), .range("f30"), Loo -- Don Guillett Microsoft MVP Excel SalesAid Software "Beebolbod" wrote in message ... How could i modify this to copy the variables from one worksheet and paste into another please? "Beebolbod" wrote: WOW. nice one. Thanks very much. "Don Guillett" wrote: try with Sheets("Summary") .Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False end with -- Don Guillett Microsoft MVP Excel SalesAid Software "Beebolbod" wrote in message ... Hi, I cant find this anywhere so please excuse possible repeat.... I would like a macro which will search for the value of a cell (in one worksheet) and simply replace a value in another worksheet with the value of another. E.G. Search for contents of B30 and replace with contents of F30. Simple i hear you say, but my current macro always contains the actual value at time of recording so if i change the value of the cells, the macro searches for and replaces the values previously stored and not the new values i'm using. Here is what i have... Sheets("Summary").Select Range("B30").Select Selection.Copy Range("F30").Select Application.CutCopyMode = False Selection.Copy Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False I would like the values (nn) and 20 to be dynamic and always refer to what's in the range("B30") and ("F30) rather than insert fixed values (taken at time of recording). Many thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search & Replace macro
Hi,
I tried that but it just inserted loads of values all over every sheet. here's my code... With Sheets("R11Variables") .Cells.Replace Sheets("R11ScriptOutput").Range("c6"), .Range("a6"), LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End With I know it's probably bad coding but i'm using multiple with statements to alter from c6 to c20 and a6 to a20. Doesn't affect it without the reference to the sheet though, just thought i'd let you know in case it makes a difference. "Don Guillett" wrote: Just reference them ..Cells.Replace sheets("othersheetname").range("b30"), .range("f30"), Loo -- Don Guillett Microsoft MVP Excel SalesAid Software "Beebolbod" wrote in message ... How could i modify this to copy the variables from one worksheet and paste into another please? "Beebolbod" wrote: WOW. nice one. Thanks very much. "Don Guillett" wrote: try with Sheets("Summary") .Cells.Replace .range("b30"), .range("f30"), LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False end with -- Don Guillett Microsoft MVP Excel SalesAid Software "Beebolbod" wrote in message ... Hi, I cant find this anywhere so please excuse possible repeat.... I would like a macro which will search for the value of a cell (in one worksheet) and simply replace a value in another worksheet with the value of another. E.G. Search for contents of B30 and replace with contents of F30. Simple i hear you say, but my current macro always contains the actual value at time of recording so if i change the value of the cells, the macro searches for and replaces the values previously stored and not the new values i'm using. Here is what i have... Sheets("Summary").Select Range("B30").Select Selection.Copy Range("F30").Select Application.CutCopyMode = False Selection.Copy Cells.Replace What:="(nn)", Replacement:="20", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False I would like the values (nn) and 20 to be dynamic and always refer to what's in the range("B30") and ("F30) rather than insert fixed values (taken at time of recording). Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Search and Replace | Excel Discussion (Misc queries) | |||
Variable in a Search and Replace macro | Excel Programming | |||
Macro to Search and Replace | Excel Programming | |||
SEARCH & REPLACE MACRO | Excel Programming | |||
Macro search replace question | Excel Programming |