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''.

Find/Replace Macro; Need Loop
 

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''.

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''.

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''.

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''.

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''.

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''.

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''.