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?
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.
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.
I have multiple strings in a selection set. My goal is to replace the decimal point with null for a specific requirement. The requirement is this: "#.## "(Meaning: NUmeric.NumericNumericSpace)
Example String #1: 2.5 kips <---no change Example String #2: 0.6 %%C <---no change Example String #3: W1.4 <---no change Example String #4: 0.086 SQ IN <---no change Example String #5: 6.2.2 <---no change Example String #6: SPC1.20 <---This one should be changed to: SPC120 Example String #7: 1.2000 <---no change
Now the real complicated string(multiple decimal points found in the same string.) Example String # 8: 2.5 kips with 0.6 %%C using W1.4 mesh with 0.086 SQ IN. See 6.2.2 on SPC1.20 for series 1.2000. #8 should changed to: 2.5 kips with 0.6 %%C using W1.4 mesh with 0.086 SQ IN. See 6.2.2 on SPC120 for series 1.2000. <---Note:SPC120 change. SPC1.20 is the ony part of the string that meets "#.## "(Meaning: NUmeric.NumericNumericSpace)
I have some data in Column L starting from L5, some data in Column C starting from C5 and some data Column D starting from D5.
What I need is to replace the cell values in column K with adjacent cell values in column J if the adjacent cells in column L,C and D fulfills the criteria of GBP, GBp and cell values starting with GB(for column D) respectively. I need to do this via VBA.
Initially column C Column D Column J Column k Column L
GB123 GB 1 2 GBP
Desired results column C Column D Column J Column K Column L
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.
Is it possible to take a list of numbers then match those numbers to another list of numbers as a whole? I'm trying to make changes to my code and take some of the advice from previous threads, but I can't visualize it. I'm trying to get away from matching each list of numbers to each other line by line. I have an existing sheet (Master) that has several col's of data. Then I have another worksheet that has updated info that I need combined into the master. What I've done so far is added the information to the bottom of my existing data and sorted.
Now I have setup an advanced filter to display the dupes. Here is where it gets tricky..I can't just delete dupes b/c there are cols to the right that have history that cannot be deleted. So I need to copy ranges A to J of the new data and replace the old data within that range.
I've attatched the workbook here with some dummy values in place of financial figures (We don't base our profit on RAND() formula's )
Essentially, I would like to search for CAN in A and SER in B and if they're found, to alter them to SYD (A) and CAN (2). It's essentially an automatic find and replace, but based on 2 criterias rather than one.
I have two groups of numbers. The first group is x amount of rows and each row has 6 numbers. The second group is x amount of rows and each row is 7 numbers.
Each number from the first group needs to check to see if it exists in the second group. If it does exist, the cell should change to a highlighted cell, or switch to an 'X' or something else that identifies it has been matched. Note: Numbers may be duplicated in both groups. Is there an easy way to do this, or will it involve programming? I'm attaching an example of the "group" setup.
I have various formulas (such as: =(D3-$E$3)/$E$3 ) which continues for each row (IE next row 3 is replaced with 4 etc)
I have made som errors when I made the formulas and therefore I have the following questions:
1. Is it possible to find and replace all E's with another letter (F in my case). I haven't been able to resolve this, when it has to be done inside formulas? The optimal would be if I could set 'Display formulas' under tools, and find and replace in the formulas displayed.
2. Is there a smart way of swapping D3 with E3 for all formulas? (So the result will be: =($E$3-D3)/$E$3 and etc for all formulas)
Is there a way in which you can use two cells (a2 & b2 for example) with a button to trigger a macro which will search for a2 and replace with b2 over a certain range of cells? unless there is a function to do it of course, which i would prefer!!
I have a workbook (materials forecasting) that automatically pulls from another workbook (production schedule).
The materials forecasting workbooks are named in regards to the current Monday (ie 02-02-09.xls, 02-09-09.xls, etc). The production schedules are named in accordance with the financial periods and weeks, ie PD1WK1.xls, PD1WK2.xls, PD1WK3.xls, PD1WK4.xls, PD2WK1.xls, etc
The materials forecasting sheet looks two weeks out.
EXAMPLE (pretend that today is the first day of PD1WK1)
Cell C1 will reference PD1WK1.xls Cell C2 will reference PD1WK2.xls
Each week I copy the old materials forecasting sheet and rename it to the current week (ie I take 02-02-09.xls and rename it to 02-09-09.xls). I have a bunch of macros set up to move my deliveries up a week and what not. One macro I would like to set up, however, is that by putting a value in a cell the find and replace macro will work.
EXAMPLE Cell A1 will reference PD1WK2.xls Cell A2 will reference PD1WK3.xls Cell C1 will reference PD1WK1.xls Cell C2 will reference PD1WK2.xls
Then I would have the macro find all instances of whatever value is in cell C2 and replace it with whatever value is in cell A2. Then likewise fore C1 and A1 respectively. (If you think about what I am doing here... all I am doing is moving the schedule up one week so that I am forecasting using the right schedule).
I can manually do this but I would prefer a macro based on a simple user input for when people fill in for me.
I've got a piece of code I'm stuck on. Basically, I have a range of data and most cells are empty. However, I want to replace all the nonblank cells with an X. I can't quite figure out how to have the code replace something non-specific...
I am attaching a sample excel workbook for reference. I have a set of coded data in numbers in ColumnA, and want to replace them with actual names which are in Column G.
As explained before, I set up columns such that all template number codes are in Column F and names in Column G. The actual raw data in Column A.
However the macro is giving me wrong replacements i.e. if the coded number was 21, its replacement is name2name1 not name 21, I hope you understand what I am saying.
It will save me bucket loads of time as I have a multiple similar columns. I am a novice in macros so I am not sure how to find full numbers (21) and replace with the specific name.
Sub FindReplace() Dim Frange As Range Dim Fr As Range Set Frange = Range("F1", Range("F65536").End(xlUp)) For Each Fr In Frange Columns("A:A").Replace What:=Fr, Replacement:=Fr.Offset(0, 1), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next Fr