Multiple Find And Replace Over An Array Of Sheets
Jul 6, 2007
I have a list of cell entries that I want to find and replace with different text or a number.
My code below is 4 of them.
I am trying to do the replace over 3 different sheets at the same time but I am only changing the 1st sheet with my efforts.
Sub Find_And_Replace()
Sheets( Array("Resolution", "Response", "Open")).Select
Sheets("Resolution").Activate
Cells.Replace What:="1 Widespread*", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="2 Critical*", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="3 Non*", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(Array("Resolution", "Response", "Open")).Select
Cells.Replace What:="4 Require*", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
View 8 Replies
ADVERTISEMENT
Dec 11, 2013
Following Excel task I am trying to complete:
I have an Excel file with multiple sheets and I want to find and replace matching cell data on the same row across all of the sheets. For example, I have two columns, Column A and Column C and 10 sheets. I want to only replace the content in Column A if text matches both Column A and Column C on the same row. So, I want to be able to search for the following data across all sheets:
Column A = "car"
Column C = "yellow"
If both "car" and "yellow" are found in Column A and Column C on the same row, then replace "car" in Column A with "truck".
Is there a way to do this automatically as I have few hundred to find and replace?
View 5 Replies
View Related
May 27, 2014
I have an excel workbook with 60 sheets (each contain data in the same categories and in the same column locations, just different information on each sheet). What would the VBA code look like if I wanted to manually enter the find and replace values and perform the function (find and replace) across multiple sheets in the workbook?
View 5 Replies
View Related
Dec 19, 2009
I have a list of codes which I want to swap into more meaningful names. For instance, say
A32
G43
R54
I want to do a find replace to turn these into
Potatoes
Carrots
Onions
Is it possible to have these written into a single piece of code ? Or, do I need to have separate pieces of code for each Find/Replace ?
View 3 Replies
View Related
Jan 28, 2014
I am working on data that needs to be cleansed of the symbols i.e. *&/- etc so I am hoping that I can automate this as their are over 30,000 rows of data and takes time to go through each find and replace.
View 9 Replies
View Related
Jan 30, 2008
I have in sheet1:
Item QTY
A 1
B 10
C 0
In sheet2 I have
Item QTY
B
View 6 Replies
View Related
Jan 10, 2007
I am attempting to run a macro that will do a find and replace on a protected sheet but this is not working. I am using the following code to Protect all Sheets, which I found on this site when workbook opens using the Workbook Open Event and uses UserInterFaceOnly :
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="111", _
UserInterFaceOnly:=True
Next wSheet
End Sub
And then a find and replace similar to this:
Sub FindAndReplace()
Selection.Replace What:="w", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
View 6 Replies
View Related
Sep 10, 2009
I need to recode 30+ files the have the old account numbers and I need to replace them with new codes ie
Old New
123456 10_1240
256789 12_1130
789123 15_1122
I have an excel spreadsheet which contains some 800 accounts in the format above showing old account number in column A and the new account number in column B. How would I write the VBA code to search all files in a specified folder search all rows in column E and find/replace using my spreadsheet containing the mapping details as above?
or in the very least just be able to run a macro to replace all the old codes in an individual spreadsheet.
View 13 Replies
View Related
Feb 15, 2008
I want to have a macro in excel where it will do a simple find and replace, except I want it to search for more than one word and replace it with one eg, I want it to look for any month and replace it with january.
View 4 Replies
View Related
Apr 16, 2014
I would like to search for numbers and replace them with text (multiple entries).
I tried doing this with vlookup, but for some reasons i dont get the good values.
View 5 Replies
View Related
Nov 29, 2012
I have two worksheets in same workbook.
Sheet1 on columnA contains my data.
Sheet2 on columnA contains the entries I am going to find on Sheet1 colA
columnB contains the entries I am going to replace with
I am currently working with these code but seems I cant make it work:
[Code] .........
View 5 Replies
View Related
Oct 28, 2011
I have been put together some simple coding to find and replace a value (1) that i have obtained from a pivot table. My aim was to replace any "1" with the column header. The reason for this is that the cell(s) will be used in a VLookup at a late stage.
My problem is that I have the code to work for a single cell, but I want it to work for multiple columns so I don not have to create multiple macros.
I need to go from Column C to AW. I have a total row at the end - hence why I am using 'Step - 1'.
Code:
Sub FindReplace()
Dim i, lRow
Application.Calculation = xlCalculationManual
[Code]....
View 7 Replies
View Related
Nov 9, 2012
I currently have two lists I am working with. One is a big mix of many different names, names which all need to be replaced.
I have another list which has all of these original names included in one column and the names which should replace those names in the column next to it.
Is there a way to go down this list and have excel find and replace every name in the sheet with its corresponding name without having to do it manually over and over?
View 1 Replies
View Related
Oct 27, 2009
say that i have alist of data in "A2:A", which are a string file (text) and i want to rename each data to a certain name that defined in range "B1:B2" (String file too).
For Example: for range
C2 = S16e
C3 = 16/E
C4 = 18W
C5 = Site18 w
& The target name is to be:
B1=16E
B2=18W
For case above, i need to replace C2&C3 in to 16E, while C4&C5 to be 18W.
View 8 Replies
View Related
Apr 1, 2012
Is there a way to do a "find & replace" across multiple pages in an excel workbook?
I am trying to update 1 particular data-set, and need to do it across all my pages...just spent 30 minutes doing find & replace on each page.
View 4 Replies
View Related
Oct 17, 2013
I looked everywhere but did not find any tools to complete the task I want, "convert" FW ruleset that look likes:
access-list outside_access_in extended permit tcp 192.168.0.10 255.255.255.254 host 172.23.34.90 eq 3978
access-list outside_access_in extended permit tcp 192.168.0.10 255.255.255.254 host 172.23.34.89 eq https
access-list outside_access_in extended permit tcp 192.168.0.10 255.255.255.254 host 172.23.34.89 eq 3978
I do want to replace all the IPs with a new values like:Original
New
172.23.34.89
172.1.1.1
172.23.34.90
172.1.1.25
172.23.34.43
172.5.2.1
192.168.0.10
192.168.0.10
192.168.0.108
192.168.0.95
192.168.0.130
192.168.2.21
View 1 Replies
View Related
Mar 13, 2014
So I have a list of organizations in Column A, with multiple names (anywhere from 0 to 50 names) for each org. in Column B.
Org
Names
Company A
Brown, Jones, Smith, West
[Code]...
I want to do a find and replace for all of Column B, where all the names are replaced with their respective color values. If possible, I'd like this to all happen with the individual cells (so for example, B3 might go from "Brown, Jones, Smith" to "Red, Red, Blue").
If that's not possible, I could divide all the names into individual cells and then find and replace.
View 3 Replies
View Related
Jul 23, 2014
I'm trying to summarize some data across 12 sheets
I'm trying to look up values against dates and specific items, however they repeat across different sheets.
The attachment has more detail : Lookup x across multiple sheets bring back value.xlsx
View 2 Replies
View Related
Mar 17, 2014
code to find/replace the letters: "A" with "Active", "P" with "Contract", and "C" with "Settled sale", all in column "Q".
View 2 Replies
View Related
Sep 13, 2010
I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.
View 9 Replies
View Related
Apr 10, 2014
I am currently using the array formula to find out the most recent date a business as referred a deal. I have been using the below array formula to figure this out...
{=MAX(IF((Opps!A:A=[@Company])*(Opps!B:B=[@[Full Name]]),Opps!G:G,0))}
However, we have now have two types of deals. Opps and Suspects. SO now i need to figure out the most recent date from Opps!G:G and Suspects!G:G... regardless of what the deal type is. Hope that makes sense.
Basically i need to have a formula along the same lines as above but MAX date from if conditions from Opps OR conditions from Suspects
View 4 Replies
View Related
Dec 5, 2011
I am trying to graph data from an Array of sheets. I keep getting many errors... I tried using Cases too but this failed, it graphed the first Sheet but nothing else.....
Before Array Works! (Uses Cases)
Code:
Sub GraphTest4()
Dim LastRowGraph1 As Long
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.Name
[Code] .........
View 8 Replies
View Related
Jan 3, 2012
I am trying to use the SUM IF Array formula to sum a group of numbers that fall under a heading of reference numbers over several sheets of data. For example purposes lets say my spreadsheet looks something like the below.
A
B
C
D
E
1
2600000248391
2600000393805
[code]......
The first two digits of the heading numbers are the criteria I am trying to use to separate and sum the data. For example I need to sum the value of the data below headings that falls between 1400000000000 and 1499999999999.
For the example above I used the below formula for the current Sheet and it works fine.
{=SUM(IF(A1:E1>="1400000000000",IF(A1:E1="1400000000000",IF(Sheet1!A1:E1,Sheet2!A1:E1
View 5 Replies
View Related
Feb 5, 2009
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
View 6 Replies
View Related
Jul 2, 2013
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?
View 4 Replies
View Related
Feb 5, 2014
[Code] .....
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
View 6 Replies
View Related
Apr 20, 2006
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
View 3 Replies
View Related
Oct 2, 2012
In Sheet1 A3 values which I want to find value
In sheet2,3,4 are DB
For example I looking for AVAI and if AVAI is in Sheet2 copy or just showing a row where is AVAI value and these VBA should find in all Sheets (2,3,4).
Just I want to find data in other sheet and show in one sheet.
View 1 Replies
View Related
Jul 31, 2008
I am using the following code to search a worksheet for a 10 digit number (derived from input box). I would like to expand the search to more work sheets (i.e. Sheets(4) to sheet count). There will only be one entry for each input number in the above work sheets. What is the best way to ammend the code?
num = InputBox("Scan Transfer Tube")
If num = "" Then Exit Sub
Set r = Cells.Find(What:=num, After:=Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
Rows(r.Row).Copy
Sheets(2).Range("A2:S2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(3).PrintOut Copies:=1, Collate:=True
View 4 Replies
View Related
Jul 13, 2009
Im creating a list of cash and carry places to buy drinks but im so clueless on how to go about doing it.
Heres the situation:
In sheet 1 I have a list of Drinks and the prices the shops are selling it for.
I have duplicates of the drinks so say for bacardi i would have one row with one shop with its price and another shop with another price. I cant put it into columns because there is other information such as the quantity the shop sells in one box etc.
On another sheet (sheet2) i have a kind of shopping list. This has all the drinks listed in Sheet 1 as mentioned above but NO DUPLICATES. What i need it to do is find the drink is Sheet 1 and pick the row with the lowest price from the multiple entries and copy that price and shop name over to sheet2.
I hope this is easy to understand. Please let me know if I need to explain some bits again. Im trying my best to figure out how to do this. I have no clue how to do it in Access.
View 14 Replies
View Related