Replace Function In VBA
Apr 5, 2009
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:
[url]
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.
View 9 Replies
ADVERTISEMENT
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
May 22, 2013
I'm wanting to use Excel's built in replace function to replace ANY date with "Call:"
I'm not going to go into details about why, but I cannot use a code, as I only want to change them at specific times.
The dates are currently formatted as 12/09/2009. So I need to change the 12/09/2009 and any other date there may be to "call:" without having to go through every possible date.
View 9 Replies
View Related
May 1, 2007
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.
View 7 Replies
View Related
Aug 1, 2007
=IF(ISNA(INDEX($D$3:$D$100,MATCH(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)),"",(INDEX($D$3:$D$100,MATC H(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)))
what i'm trying to do is to replace #N/A with blank. Taken the above from vlookup examples but does not seems to work.
View 9 Replies
View Related
Jul 6, 2009
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.
View 9 Replies
View Related
Jan 11, 2010
See my file. I want to calcuate a % needed (20%) to add to a another material. Should be easy but I can not figure it out.
View 3 Replies
View Related
Nov 25, 2012
I just want to replace the offset with find function. I have attached the sample file with the code. Just need a little change.
View 5 Replies
View Related
Mar 14, 2014
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.
{=MAX(IF($H$2:$H$22="sedimentary rocks",$W$2:$W$22))}
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.
View 2 Replies
View Related
Mar 2, 2007
I m using SUBSTITUTE function to replace commas with fullstop so I can multiply the end result with a number. But when I try to multiple for example B2 (0.1831) with 5, i get the VALUE! error.
Value Real Value (after substitute function)
0,1831 0.1831
23,3333 23.3333
12,5199 12.5199
5,5000 5.5000
20,5999 20.5999
24,4671 24.4671
200,0000200.0000
2,5386 2.5386
0,4000 0.4000
1,5019 1.5019
how I can resolve this so I can use the real values for computations (eg Real value *5), without having the VALUE! error message.
View 12 Replies
View Related
Oct 7, 2008
Plan numbers are contained in Row 1 (1518, etc). Options are listed in Column A (L101, L102, etc). I want to add the costs of each option, resulting in the total and each option code only listed once, with that total, below each plan. Example:.......
View 2 Replies
View Related
Jul 4, 2009
I am working on a spreadsheet which contains a number of reference data sheets (named “Reference data 2009”, “Reference data 2010” etc). As their names suggest, these sheets contain reference data applicable to the particular year. This reference data is used to perform various calculations in a “Calculations” sheet.
On the “Calculations” sheet, the user specifies the year for which they wish to perform calculations. At present, I am using the volatile INDIRECT function to perform various HLOOKUP calculations along the following lines:
=IF(D15>=HLOOKUP('Detailed net pay calculations'!D16, INDIRECT("'Reference data" & 'Detailed method'!$C$2 & 'Detailed net pay calculations'!D1 & "'!$B$43:$Y$52"), 4, FALSE), “Do something”, “Do something else”)
The reason I'm using the INDIRECT function is to identify the sheet with the appropriate year (hence "Reference data"&'Detailed method'!$C$2&'Detailed net pay calculations'!D1 which could be interpreted by Excel as "'Reference data 2009NEW" or "'Reference data 2010", depending on whether there's text in cell D1).
My query
The function I'm using is working perfectly fine but I am wondering if it is possible to replace the INDIRECT function (in red) with INDEX or another non-volatile function in order to reduce the performance impact (I have a fairly large number of these types of functions).
View 2 Replies
View Related
Sep 9, 2013
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.
View 5 Replies
View Related
Oct 8, 2007
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.
View 8 Replies
View Related
Jun 2, 2009
I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.
What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.
Is this possible?
View 6 Replies
View Related
Mar 29, 2012
Any way to use a find and replace function involving a cell reference. I have a spreadsheet with ton of junky text burying information that I want. Here is a simplified example
A B
Book 345353hg dgdgsdgfd fff Book belongs to Jim
Sneaker fdg4 Sneaker is worn by Jan
Gum dfg s d e te4345Gum tastes great
Cake jklsjflsjfjikCake smells delicious
The pattern is that the A1 text appears in the B2 text. What I want is
Find *A1
Replace with blank
Find *A2
Replace with blank
and etc.
So that in the end, I get this:
B2
belongs to Jim
is worn by Jan
tastes great
smells delicious
I've looked at the functions of find, replace, substitute, left, and right and I can't seem to find the right one to do what I need.
View 3 Replies
View Related
Aug 10, 2012
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
View 3 Replies
View Related
Feb 28, 2013
I have special character that I removed with =CLEAN formula.
It was only one character which represents carriage return. It looks like one little square with question mark inside.
After I applied =CLEAN formula it disappeared, but now I don't have space between these two words.
How could I replace this special character with space?
View 9 Replies
View Related
Apr 8, 2014
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.
View 1 Replies
View Related
Jun 10, 2009
I'm getting a Sub or Function not defined error on the code below. It highlights the Replace Function as the problem, have also tried the substitute function too with the same result.
View 11 Replies
View Related
Mar 3, 2014
I'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. I still get the same error.
[Code] ............
View 1 Replies
View Related
Jun 11, 2014
I've recently been making a macro in visual basic that loops through all my excel files and replaces an old company name to a new company name and It's working great, well except for one thing... It always misses one string in the file and it leaves me with 90% of the file corrected. I think that the string might be a part of a merged range so Range.Find is not able to find it. I was suggested to use this code:
[Code] ......
Because that's the code that is generated when using the replace function in MS Excel 2010, but I keep getting syntax errors?
View 2 Replies
View Related
May 28, 2009
I'm trying to put the "Find" Function inside my worksheet to make it easier for the end user. How would I do this? See worksheet for example.
View 2 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
Jun 15, 2006
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.
View 2 Replies
View Related
Feb 15, 2007
trying to transfer a macro from Win XP, Excel 2003(?) to Mac OS X, Excel X, my program crashes due to missing functions in Excel X.
Apparently, there is no such function as "Replace()" in Mac Excel....
'these lines do not work on Mac Excel
lfdNrStr = Replace(lfdNrStr, "(", "") 'takes out the brakes
lfdNrStr = Replace(lfdNrStr, ")", "") 'takes out the brakes
lfdNrStr = Replace(lfdNrStr, " ", "") 'takes out the empty spaces
lfdNr = CInt(lfdNrStr)
View 9 Replies
View Related
Mar 18, 2009
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.
View 2 Replies
View Related
May 18, 2009
I have over 16000 cells with different information in them, each cell is unique. There are parts of these cells that have similar information.
I am looking to search part of the cell, and replace the whole cell with my new description, so I can run pivot tables on it.
View 9 Replies
View Related
