Excel 2010 :: Macro To Perform Find And Replace Function
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
ADVERTISEMENT
Feb 15, 2012
I have Excel 2010, and a dual monitor setup, with the left monitor being the main one. I have my spreadsheets on the right monitor.
When I open a Find and Replace, the dialog box always goes to monitor #1 (left); I want it on monitor #2.
Is there a way to default it to the "right" monitor?
View 3 Replies
View Related
Oct 1, 2012
I have an excel workbook where some sheets have a column called "Name" in Row 3. The column where "Name" appears shifts based upon other criteria so it's not set within 1 specific column.
The real data for the "Name" column starts in Row 5. Is it possible to create a macro that looks in Row 3 for "Name", then once it finds that column, it does a find and replace from Row 5 to the end of the data and replaces every space with a ^.
For example, if I have:
Row 3 Name
Row 4
Row 5 John Smith
Row 6 Jane Doe
Row 7 John Doe
Row 8 Jane Smith
I'd like the data to become:
Row 3 Name
Row 4
Row 5 John^Smith
Row 6 Jane^Doe
Row 7 John^Doe
Row 8 Jane^Smith
I'm using Excel 2010 if that impacts anything.
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
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 21, 2014
where to find a spreadsheet which has a macro to perform the FFT on data with > 4096 points? It seems Excel's internal FFT is limited to this amount. I searched the forum and elsewhere on web and have not found what I am looking for. I wish to enter the data in a column of the spreadsheet, preferably in a named range such as "Data" and have a macro perform the FFT.
View 3 Replies
View Related
Jan 31, 2013
Exel 2010 cells formatted to Time
need to look at h1 and h2 if no data ignore
other wise add data together if total exceeds 08:45
then deduct 45min from h1
if combined the are less than 8:45 then h3 must equal 8:00 by altering h1 only
Example
h1= 5:00
h2= 2:00
h3= 8:00
h1=
h2=
h3=
h1= 8:00
h2= 2:00
h3= 7:15
View 3 Replies
View Related
Jul 19, 2013
I am looking to implement the "Compare Documents" function (available in Word 2010) in Excel 2010 through VBA programming between different text contained in two cells.
In Word this function works quite well (not perfectly), but it highlights in different ways which part has been deleted and which one has been added between an "original" document and a "revised" one.
For the nature of my job, I need to do this on a daily basis and I used to output text from Excel to Word, then compare the two text, and then copy it back to Excel.
Here comes the problem: since in Word the text is formatted (and what I'm looking for is formatted/highlighted text as output), I can't just paste it in Excel as it is: any editing, merging, splitting done on the pasted text (that eventually I need to do) makes the formatting disappear (above all with VBA functions, that can only output data and can't format it).
In other words: given two cells containing different text, I would like to be able to fill a third cell with text formatted accordingly to the TextDiff output between the two original cell.
E.g.:
INPUT:
Cells(1,1).Value2 = "my name is Andrea and I like jogging" (original)
Cells(1,2).Value2 = "my name is Giovanni and I like running" (revised)
OUTPUT:
Cells(1,3) wll contain: "my name is AndreaGiovanni and I like joggingrunning"
Obviously, since UDF doesn't allow formatting of cells, I would need to adjust the main Sub for each pair of document I have to revise, but that won't be the problem: what I need is the engine. It's been two years and a half that I do advanced VBA programming at work but it looks like I can't grasp the rationale behind the LCS algorithm.
View 9 Replies
View Related
Jan 22, 2013
I want to use the find function on a range and search for a "," character. If I get a "," I want to return a True otherwise false.
I tried just recording a macro using the find function but no code shows up.
how to use the find feature in a macro, can this be done?
excel 2010
View 4 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
Apr 25, 2014
I'm in Excel 2010, and the cell with the date I want to work from is H22.
I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
I should also probably note that the date in H22 is the result of another function.
=EDATE(G22,12)
View 5 Replies
View Related
May 19, 2014
See attached image.
So what I am looking for is to put a macro behind button 1 which will do the following
Enter A Default Value Of 123 In The Yellow Boxes (B7:K7)
If They Are Blank When The User Clicks The Button
Performs A Search And Replace To Replace The Values 01-01-1990 With B8, 02-01-1990 With C8 Etc Until K8
Output The Contents Of Sheet 2 To A Text File (In XAI Format) In The Following Order A1:A100, B1:B100, C1:C100
[Code] .........
View 1 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
Mar 15, 2012
I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell
[Code] ...........
View 2 Replies
View Related
Feb 8, 2013
I have got an issue. in MSE 2003, this beginning of macro worked:
Sub Consolide()
MYFOLDER = "D:DATAMypath"
In MSE 2010, it gives me an error: "Can't find project or library"
I really know that my path is defined fine, because it worked perfect before MS update.
View 1 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
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
Apr 24, 2014
In Excel 2010 I am attempting to replace values in a cell, the right two values with "00", but am having difficulties with the correct '=replace' function.
View 2 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
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Jan 24, 2014
I need to replace A1*FX! with A1/FX! within thousands of formulas (and many variants of A1)
Unfortunately whenever I attempt this is deletes the A1 (or whatever cell is being referenced)
How to stop find replace doing this?
View 4 Replies
View Related
Apr 7, 2012
I have never used VBA in word before, so I am assuming I am making it more complicated than it needs to be. I have a macro that stores 20 excel values as variables, then opens up a word document. I want to replace values in word with my values in excel. I keep getting an error with the replace part. I do not want to use bookmarks or mail merge. MSWord is a reference in excel VBA.
Code that stores excel variables (Example: Var1=Data1 and Var2=Data2)...
'Opening up the correct word document
Dim WdApp As Object, WdDoc As Object
Set WdApp = CreateObject("Word.Application")
WdApp.documents.Open ThisWorkbook.Path & "" & Range("D1").Value & ".doc"
WdApp.Visible = True
[Code] ..........
View 9 Replies
View Related
Oct 16, 2013
I am looking to make an excel script that can find and replace data.
Situation:
A--------B-----------C
Cat------Dog------Banana
Dog------Fish------Apple
Fish------Cat-------Orange
So the macro would look at the data in a cell in column B, then look at the adjacent cell in column C, and replace all instances of that data in column A with what if found in C. So the results would be:
A------------B-----------C
Orange------Dog------Banana
Banana------Fish------Apple
Apple--------Cat-------Orange
But that's not all, I would like it to not change cells in A that already have been changed once! (I'm trying this with changing the background colour)
I am at a complete loss and need to find and replace over 3000 items.
View 4 Replies
View Related
Feb 10, 2009
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.
View 11 Replies
View Related
Feb 21, 2012
I have a column of about 10,000 lines which contains a description Sheet 1 Column B. On another sheet (2) I have about 200 lines of abbrevations. In coulmn A2 is the full name, and column b is the Abbrevation. I would like to create a macro that would go down the list in A2 Sheet 2, go to Sheet 1 B find that word and replace it with Sheet 2 B2 abbrevation. Is there a way to do that?
Sheet 1
A B
680385 LEAD SET WITH GRABBER CONNECTION21379 ABSORBENT CARBON DIOXIDE SODASORB 3LB
Sheet 2
A B
WORDAbbreviationABDOMINALABDOMANGIOGRAPHICANGIO
View 4 Replies
View Related
Apr 2, 2009
There are 1000's of XML tag with values.. i want a macro which should replace all the tags(the value which is in between angel brackets should only be removed and angel bracekst also should be removed) with or without space and tag value should remain unchanged.
View 9 Replies
View Related
Dec 31, 2003
I had written a macro about two years ago that replaced longer company names with common abbreviations. I store it in Personal.xls, and it has been relatively fast (2-5 sec for most projects with < 2000 rows). However, I tried it on a file with 15,000 rows, and it choked Excel - i.e. it wouldn't run and had to be force quite (Excel XP on Windows 2000).
Here is a portion of the macro (I have about 40 companies that need to be abbreviated, names here have been simplified to protect the guilty ). I stepped through a couple of these lines to see what would happen, and each line took at least 1-2 min.
Sub ChgCompNames()
Application.ScreenUpdating = False
Cells.Replace What:="Company AAAA", Replacement:="AAAA", LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="Company BBBB", Replacement:="BBBB", LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="Company CCCC", Replacement:="CCCC", LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="Company DDDD", Replacement:="DDDD", LookAt:=xlPart, SearchOrder:=xlByRows
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Dec 1, 2005
I'm looking to simply delete certain words that are in cells, without having to delete rows, columns or the entire cell. I can use 'find and replace' and replace the particular word with a space, but I really want to delete the word. I'm using both Excel 2003 and 2000.
View 4 Replies
View Related
Aug 24, 2013
I have Excel MAC 2011 and my spreadsheet was created with multiple cell colors randomly throughout the spreadsheet.
I need to change all the royal blue colored cells to "no color" without changing the data contained in the cell. For example; Cells A5:A38 are blue, A39 is green, A40:A50 blue, A51:60 green, ... Cells B5:B15 are grey, B16:B20 are blue, B21 is yellow...
I can't find the "Advanced Options" for Find and Replace that might have allowed me to specify a color change.
View 4 Replies
View Related
