Find and Replace based on neighboring cells?
 

I've searched this forum but haven't found this issue yet.

For example: Column A has either a 1 or a 2 as the cell contents. Column B
has a bunch of various values. What I'd like to do is to Find and Replace 1
with 2 (or vice versa) but based on the contents of Column B. I can't do a
simple Find and Replace with Column A because then all of the 1's will be
replaced with 2's.

I've looked all over the place and I can't quite seem to find a solution to
this. I can of course alter everything manually, but I'm dealing with 350+
sheets with each sheet containing 2500+ rows.

Find and Replace based on neighboring cells?
 

Is there any way to use the Autofilter on column B?

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"djc" wrote:

I've searched this forum but haven't found this issue yet.

For example: Column A has either a 1 or a 2 as the cell contents. Column B
has a bunch of various values. What I'd like to do is to Find and Replace 1
with 2 (or vice versa) but based on the contents of Column B. I can't do a
simple Find and Replace with Column A because then all of the 1's will be
replaced with 2's.

I've looked all over the place and I can't quite seem to find a solution to
this. I can of course alter everything manually, but I'm dealing with 350+
sheets with each sheet containing 2500+ rows.

Find and Replace based on neighboring cells?
 

I think you need a macro. the one below is very simple and will do what what
you want

Sub findandreplace()

For Each ws In ThisWorkbook.Sheets
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
Select Case ws.Range("B" & RowCount)
Case "Option1", "Option2", "Option3"
ws.Range("B" & RowCount) = 1
Case "Option4", "Option5", "Option6"
ws.Range("B" & RowCount) = 2
End Select
Next RowCount
Next ws

"djc" wrote:

I've searched this forum but haven't found this issue yet.

For example: Column A has either a 1 or a 2 as the cell contents. Column B
has a bunch of various values. What I'd like to do is to Find and Replace 1
with 2 (or vice versa) but based on the contents of Column B. I can't do a
simple Find and Replace with Column A because then all of the 1's will be
replaced with 2's.

I've looked all over the place and I can't quite seem to find a solution to
this. I can of course alter everything manually, but I'm dealing with 350+
sheets with each sheet containing 2500+ rows.

Find and Replace based on neighboring cells?
 

I had column B instead of A in two places

Sub findandreplace()

For Each ws In ThisWorkbook.Sheets
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
Select Case ws.Range("B" & RowCount)
Case "Option1", "Option2", "Option3"
ws.Range("A" & RowCount) = 1
Case "Option4", "Option5", "Option6"
ws.Range("A" & RowCount) = 2
End Select
Next RowCount
Next ws

"djc" wrote:

I've searched this forum but haven't found this issue yet.

For example: Column A has either a 1 or a 2 as the cell contents. Column B
has a bunch of various values. What I'd like to do is to Find and Replace 1
with 2 (or vice versa) but based on the contents of Column B. I can't do a
simple Find and Replace with Column A because then all of the 1's will be
replaced with 2's.

I've looked all over the place and I can't quite seem to find a solution to
this. I can of course alter everything manually, but I'm dealing with 350+
sheets with each sheet containing 2500+ rows.

Find and Replace based on neighboring cells?
 

Thanks for your replies. I'm going to try this macro today, but I've never
used a macro before so we'll see what happens. Hopefully this will work!


"Joel" wrote:

I had column B instead of A in two places

Sub findandreplace()

For Each ws In ThisWorkbook.Sheets
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
Select Case ws.Range("B" & RowCount)
Case "Option1", "Option2", "Option3"
ws.Range("A" & RowCount) = 1
Case "Option4", "Option5", "Option6"
ws.Range("A" & RowCount) = 2
End Select
Next RowCount
Next ws

"djc" wrote:

I've searched this forum but haven't found this issue yet.

For example: Column A has either a 1 or a 2 as the cell contents. Column B
has a bunch of various values. What I'd like to do is to Find and Replace 1
with 2 (or vice versa) but based on the contents of Column B. I can't do a
simple Find and Replace with Column A because then all of the 1's will be
replaced with 2's.

I've looked all over the place and I can't quite seem to find a solution to
this. I can of course alter everything manually, but I'm dealing with 350+
sheets with each sheet containing 2500+ rows.