Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace Macro; Need Loop
Hello! I am trying to code a find/replace sub. The code below works: Sub FindReplace() ActiveSheet.Columns("A:B").Select Selection.Replace What:="Chrysler Financial Services Americas LLC", _ Replacement:="Chrysler Financial Services", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="General Motors Hourly Pension Fund", _ Replacement:="GM Hourly Pension Fund", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False €˜etc End Sub However, the list of What:="" names will change frequently. This past week there were 10 names; next week there may be many more. All names are listed on a Sheet named €˜List of names to change (1 sheet). Basically, I am trying to come up with a way of activating the Sheet named €˜List of names to change, selecting the variables (actually text/names, in column A) and then replacing the variables (actually text/names, in column B€¦the same row as Column A). By the way, all name replacements (all names will be in Column A or Column B) will happen in sheets named €˜top 20 - AFTER or €˜top Industry - AFTER (2 sheets). The names originally come from sheets named €˜top 20 - BEFORE and €˜top Industry - BEFORE. Any ideas on how to do this? Thanks so much, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace Macro; Need Loop
sub main simply scans column A .. each name is to be replaced by the name in column B for any row Sub Main dim cell as range for each cell in worksheets("List of names to change").Columns(1").Cells if cell.Value<"" then FindReplace() cell.Value, cell.Offset(,1).Value end if next End Sub Sub FindReplace(FindName as string, replacename as string) ActiveSheet.Columns("A:B").Replace What:=findname, _ Replacement:=replacename, LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub However, the list of What:="" names will change frequently. This past week there were 10 names; next week there may be many more. All names are listed on a Sheet named €˜List of names to change (1 sheet). Basically, I am trying to come up with a way of activating the Sheet named €˜List of names to change, selecting the variables (actually text/names, in column A) and then replacing the variables (actually text/names, in column B€¦the same row as Column A). By the way, all name replacements (all names will be in Column A or Column B) will happen in sheets named €˜top 20 - AFTER or €˜top Industry - AFTER (2 sheets). The names originally come from sheets named €˜top 20 - BEFORE and €˜top Industry - BEFORE. Any ideas on how to do this? Thanks so much, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace Macro; Need Loop
Sub ReplaceIfChecked() 'SalesAidSoftware
With Sheets("List") lr = .Cells(Rows.Count, "a").End(xlUp).Row For Each c In .Range("a2:a" & lr) If LCase(c.Offset(, 2)) = "x" Then Sheets("Top20").Columns(1).Replace c, c.Offset(, 1) Sheets("TopIndustry").Columns(2).Replace c, c.Offset(, 1) End If Next c End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "ryguy7272" wrote in message ... Hello! I am trying to code a find/replace sub. The code below works: Sub FindReplace() ActiveSheet.Columns("A:B").Select Selection.Replace What:="Chrysler Financial Services Americas LLC", _ Replacement:="Chrysler Financial Services", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Replace What:="General Motors Hourly Pension Fund", _ Replacement:="GM Hourly Pension Fund", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False €˜etc End Sub However, the list of What:="" names will change frequently. This past week there were 10 names; next week there may be many more. All names are listed on a Sheet named €˜List of names to change (1 sheet). Basically, I am trying to come up with a way of activating the Sheet named €˜List of names to change, selecting the variables (actually text/names, in column A) and then replacing the variables (actually text/names, in column B€¦the same row as Column A). By the way, all name replacements (all names will be in Column A or Column B) will happen in sheets named €˜top 20 - AFTER or €˜top Industry - AFTER (2 sheets). The names originally come from sheets named €˜top 20 - BEFORE and €˜top Industry - BEFORE. Any ideas on how to do this? Thanks so much, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace Macro; Need Loop
Thank you very much Don!! Great stuff. However, I wanted the computer to figure it out, not the person. Nevertheless, the 'x' is very cool. Patrick, this line is red: FindReplace() cell.Value, cell.Offset(,1).Value What do I need to change it to, to get it working? Thanks again, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Patrick Molloy" wrote: sub main simply scans column A .. each name is to be replaced by the name in column B for any row Sub Main dim cell as range for each cell in worksheets("List of names to change").Columns(1").Cells if cell.Value<"" then FindReplace() cell.Value, cell.Offset(,1).Value end if next End Sub Sub FindReplace(FindName as string, replacename as string) ActiveSheet.Columns("A:B").Replace What:=findname, _ Replacement:=replacename, LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub However, the list of What:="" names will change frequently. This past week there were 10 names; next week there may be many more. All names are listed on a Sheet named €˜List of names to change (1 sheet). Basically, I am trying to come up with a way of activating the Sheet named €˜List of names to change, selecting the variables (actually text/names, in column A) and then replacing the variables (actually text/names, in column B€¦the same row as Column A). By the way, all name replacements (all names will be in Column A or Column B) will happen in sheets named €˜top 20 - AFTER or €˜top Industry - AFTER (2 sheets). The names originally come from sheets named €˜top 20 - BEFORE and €˜top Industry - BEFORE. Any ideas on how to do this? Thanks so much, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace Macro; Need Loop
You didn't SAY so. Just take out the if Sub ReplaceIfChecked() 'SalesAidSoftware With Sheets("List") lr = .Cells(Rows.Count, "a").End(xlUp).Row For Each c In .Range("a2:a" & lr) Sheets("Top20").Columns(1).Replace c, c.Offset(, 1) Sheets("TopIndustry").Columns(2).Replace c, c.Offset(, 1) Next c End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "ryguy7272" wrote in message ... Thank you very much Don!! Great stuff. However, I wanted the computer to figure it out, not the person. Nevertheless, the 'x' is very cool. Patrick, this line is red: FindReplace() cell.Value, cell.Offset(,1).Value What do I need to change it to, to get it working? Thanks again, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Patrick Molloy" wrote: sub main simply scans column A .. each name is to be replaced by the name in column B for any row Sub Main dim cell as range for each cell in worksheets("List of names to change").Columns(1").Cells if cell.Value<"" then FindReplace() cell.Value, cell.Offset(,1).Value end if next End Sub Sub FindReplace(FindName as string, replacename as string) ActiveSheet.Columns("A:B").Replace What:=findname, _ Replacement:=replacename, LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub However, the list of What:="" names will change frequently. This past week there were 10 names; next week there may be many more. All names are listed on a Sheet named €˜List of names to change (1 sheet). Basically, I am trying to come up with a way of activating the Sheet named €˜List of names to change, selecting the variables (actually text/names, in column A) and then replacing the variables (actually text/names, in column B€¦the same row as Column A). By the way, all name replacements (all names will be in Column A or Column B) will happen in sheets named €˜top 20 - AFTER or €˜top Industry - AFTER (2 sheets). The names originally come from sheets named €˜top 20 - BEFORE and €˜top Industry - BEFORE. Any ideas on how to do this? Thanks so much, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace Macro; Need Loop
Ah! Brilliant! Thank you sir!!!
Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Don Guillett" wrote: You didn't SAY so. Just take out the if Sub ReplaceIfChecked() 'SalesAidSoftware With Sheets("List") lr = .Cells(Rows.Count, "a").End(xlUp).Row For Each c In .Range("a2:a" & lr) Sheets("Top20").Columns(1).Replace c, c.Offset(, 1) Sheets("TopIndustry").Columns(2).Replace c, c.Offset(, 1) Next c End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "ryguy7272" wrote in message ... Thank you very much Don!! Great stuff. However, I wanted the computer to figure it out, not the person. Nevertheless, the 'x' is very cool. Patrick, this line is red: FindReplace() cell.Value, cell.Offset(,1).Value What do I need to change it to, to get it working? Thanks again, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Patrick Molloy" wrote: sub main simply scans column A .. each name is to be replaced by the name in column B for any row Sub Main dim cell as range for each cell in worksheets("List of names to change").Columns(1").Cells if cell.Value<"" then FindReplace() cell.Value, cell.Offset(,1).Value end if next End Sub Sub FindReplace(FindName as string, replacename as string) ActiveSheet.Columns("A:B").Replace What:=findname, _ Replacement:=replacename, LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub However, the list of What:="" names will change frequently. This past week there were 10 names; next week there may be many more. All names are listed on a Sheet named €˜List of names to change (1 sheet). Basically, I am trying to come up with a way of activating the Sheet named €˜List of names to change, selecting the variables (actually text/names, in column A) and then replacing the variables (actually text/names, in column B€¦the same row as Column A). By the way, all name replacements (all names will be in Column A or Column B) will happen in sheets named €˜top 20 - AFTER or €˜top Industry - AFTER (2 sheets). The names originally come from sheets named €˜top 20 - BEFORE and €˜top Industry - BEFORE. Any ideas on how to do this? Thanks so much, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace Macro; Need Loop
looks like a typo FindReplace cell.Value, cell.Offset(,1).Value "ryguy7272" wrote in message ... Thank you very much Don!! Great stuff. However, I wanted the computer to figure it out, not the person. Nevertheless, the 'x' is very cool. Patrick, this line is red: FindReplace() cell.Value, cell.Offset(,1).Value What do I need to change it to, to get it working? Thanks again, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Patrick Molloy" wrote: sub main simply scans column A .. each name is to be replaced by the name in column B for any row Sub Main dim cell as range for each cell in worksheets("List of names to change").Columns(1").Cells if cell.Value<"" then FindReplace() cell.Value, cell.Offset(,1).Value end if next End Sub Sub FindReplace(FindName as string, replacename as string) ActiveSheet.Columns("A:B").Replace What:=findname, _ Replacement:=replacename, LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub However, the list of What:="" names will change frequently. This past week there were 10 names; next week there may be many more. All names are listed on a Sheet named €˜List of names to change (1 sheet). Basically, I am trying to come up with a way of activating the Sheet named €˜List of names to change, selecting the variables (actually text/names, in column A) and then replacing the variables (actually text/names, in column B€¦the same row as Column A). By the way, all name replacements (all names will be in Column A or Column B) will happen in sheets named €˜top 20 - AFTER or €˜top Industry - AFTER (2 sheets). The names originally come from sheets named €˜top 20 - BEFORE and €˜top Industry - BEFORE. Any ideas on how to do this? Thanks so much, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace and Find & Insert macro help needed | Excel Programming | |||
Macro to Find & Replace | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Find & Replace / Loop & Vlookup | Excel Programming | |||
Find/replace macro | Excel Programming |