Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find based on cond. 4matting? Replace as ALT-Enter? (2 diff prob | Excel Discussion (Misc queries) | |||
Quickest way to find/replace based on values? | Excel Worksheet Functions | |||
find replace in multiple sheets based on namebox | Excel Discussion (Misc queries) | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
find instances & report neighboring values | Excel Discussion (Misc queries) |