Catching Errors When Using The Find / Replace Function / Replace All
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.
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.
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?
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
Here is the situation: - Large block of text in one cell (1000+ words). - I use the Find & Replace function (from the top menu) to find a specific word that may appear several times within the cell. - Excel "highlights" the cell... but not the word specifically so I have to read all the text to find that word throughout the cell.
I'm receiving this message when I attempt to use the Find, Replace function. Formula is too long". I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.
I am using a macro to find a value in a cell and if found, replace the value in the cell to the left. Thr macro works fine - unless the value I am searching for does not exist. The answer is ( Ithink) an IF statement, but I am having problems with the errors, when I try the IF.
The macro below blows up at the ActiveCell.Offset(0, -1) = "109073X line with an error "Run-time error "1004" - Application-defines or object-defined error.
I've tried a number of things I've seen from this board but have not found a way past the error.
If the value is found, the macro moves to the else statment and stops at the ActiveCell statement with the above error. If the value is not found, then the macro goto NotFound.
Sub Macro2() ' 'Range("A1").Select 'Start from the home cell Dim ValueFound As Object Set ValueFound = Cells.Find(What:="Cirrus Reversals/CREDITS") If ValueFound Is Nothing ThenGoTo NotFoundElseActiveCell.Offset(0, -1) = "109073X"'ActiveCell.FormulaR1C1 = "109073X"End If NotFound: End Sub
I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.
So for example:
Sheet1 says the following on column N: cat dog lion bear
Sheet2 says: Column A A1: Cat A2: Dog A3: Bear
Column B B1: 2 B2: 8 B3:15
I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.
I have values in row A and I have values in row C. I want to create a loop to look up xxx and replace it with the values consecutively in row C. Look at example for a better Idea. I found a way to find and replace, but I not sure how to use it with qoutes. I was thinking maybe I dont even need row A and just supply a list and excel could have the chunk of data in the code itself.
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website:
Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes
I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.
I was trying to help another user and came across this problem.
In cell A1 I have "abcd" In cell A2 I have =REPLACE(A1,4,1,"e") resulting in "abce" I tried to use = REPLACE(A1,RIGHT(A1,1),1,"e") but get #VALUE! I am wanting to use RIGHT because the number of characters is unknown.
My code here searches for a number from my (mActiveSheet) to see if it matches any number on my "Finanace" sheet. With this code, if the number on my (mActiveSheet) isnt on my " Finanace" sheet, then the proram gets a run time error 91. It happens when my SSRmatch is empty, and it blows up at the line of: If SSRmatch = SSRR, b/c my SSRmatch does not have anything in it. Can anyone help by not having it to blow up, and just move on to my code where if my SSRmatch is nothing?
Do Until z = LastRow If SSRR "" Then Sheets("Finanace").Select Dim SSRmatch As Range Set SSRmatch = Range("B5:B100").Find(What:=SSRR, After:=Cells(50, 2), LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 'I have everything I need when it is equal to true: If SSRmatch = SSRR Then
I've always used event driven functions - ie. user clicks a button and my function gets called.
What I want to do now is replace a HUGE formula that is impossible to debug with a function. So the idea is, any time the user changes any cell, the function should be called and update a certain cell with a new value.
I was wondering if it is possible to do this unique find and replace that is explained below.
I have many columns with data from our database. I have one column that has my html layout in it. I want to find within the html text data and replace it with data from another column for that row.
Example: We have [[manufacturer]] in the html area that we want to replace with data from our column called Manufacturer. So, it will replace the text in the html named [[Manufacturer]] with that rows Manufacturer data we have.
I have around 10 names which I want to replace with their code names. For Example, "Alan Richard" needs to be replaced with AR001. Problem is I don't know cell range and this name might appear more than once in the data.
I am trying to find and replace #N/A from a formula (link to another sheet) with nothing in the cell, using VBA.
Using this code it will do the first "find" Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True, SearchFormat:=False).Activate ActiveCell.ClearContents Trying this Cells.Replace What:="#N/A", Replacement:=" ", LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=True, _ ReplaceFormat:=False LookIn:= is highlighted and I get a "named argument not found" Don't understand that because LookIn:=xlValues works in the first code.
I did try and place After:=ActiveCell,before this, but that does not work. I Get the same error.
I have more then one cell with the #N/A which I would like to get rid of. I want this to work only on this one sheet. The other sheet that the formula is pulling from does have #N/A, and that is okay, I need it there.
I have a list of sales associates that are assigned numbers. For example, John Doe is 1022. The data I have to work with only shows his number 1022 but for my reports management wants the name John Doe. I have about 60 sales associates and have a VB code that 'finds and replaces'. The code is huge. It has slowly grown out of hand, with more sales associates added daily. I also have 22 sheets in one workbook with specific columns to search for the numbers to replace with the names.
This is what I would like to do. Have a sheet named associates, as more associates are added I would just have to add their name and number. Have two columns with headers column A - Associates column B Associate #. Name ranges for the two columns and write a code that says look in the sheet associate, at named range 'numbertoname' and if the current sheet 'total sales' I am in has the number 1022 in column C replace it with the name John Doe. It seems like it should be easy but.......I have tried and tried today.
I am facing a strange problem with Find and Replace function of Excel. I converted a PDF document into a Word document. I copied the tables in the Word document to an Excel document. As the data had many ‘*’ characters, which I did not want, I used Find and Replace function to get rid of them. However, to my surprise, two things were happening.
1.When I tried to replace ‘*’ in a cell with ‘ Construction revenue ********************** ’, it was deleting all the contents of the cell. 2.When I tried to find all the cells with ‘*’ , it was selecting cells even without ‘*’
I tried various things like changing the format, pasting only values..etc, but nothing worked. Finally I had to use the Replace function of VBA to do the task. For further analysis I am attaching my Excel file with that data.
I am working with a lot of data and have been making a table of statistical analysis for several rock types. I have it worked out to where i can filter the data and with the use of a bunch of IF functions, below, and it keeps everything the way I want.
The problem is I have to introduce new rock types, but if I just copy the formulas over i then have to go through hundreds of cells and switch sedimentary rocks to volcanic or whatever new class i have.
How this can be done faster. Maybe some way to say "find "text" within this range and replace" i can do it for actual values but when its embedded in a function im not sure.