Find and replace script / macro?
I'm not sure what type of function is needed for what I'm describing below. I'm thinking it is a script or macro? I don't know anything about writing scripts or macros and I am quite new to excel.
I'm trying to learn chinese and am making chinese flashcards for myself.
The fact that some of the cells have chinese characters probably doesn't matter, but I just want to be thorough in my description....
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 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
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 RelatedI 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
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] .........
This macro finds and replaces multiple values found on another sheet. however, it does not only take the exact value, also others.
Example:
If i want to change "Example 1", "Example 2", ... "Example 10" into "One", "two", etc. Example 10 then changes into "One 0" because it changes the Example 1 (without the 0 in 10) into One. Is there any way to alter this (or do you have another macro to replace multiple values?
Here is the find / replace vba I am using.
Sub Replace()
Cells.Replace What:=Chr(19), Replacement:="-", LookAt:=xlPart
Cells.Replace What:=Chr(24), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(145), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(146), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub
It makes the replacements up to line 37, then stops. If you then delete the first 37 rows and re-run the macro, it again performs replacements, but only on the next 37 rows.
I would like the macro to do the whole sheet...
way to find/ replace some null values in a range of selected cells in a column. I would like to select a first and last cell, and replace the value -999.25 with the average of the cells that are above and below it. If there are more than one -999.25 cells, I would like to do the same thing with the average of the cells that above and below the first and last -999.25.
I would also like the same macro to replace any value that is over 200 with the average of the cells above and below it...
The error is a Run-Time 450 (Too many arguments or invalid property assignment), when I click debug it highlights With Selection.Find
Sub TestTemplates()
Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim appWD As Object
Set appWD = CreateObject("Word.Application")
Sheets("Sheet1").Activate
s1 = ActiveSheet.Range("Name")
s2 = ActiveSheet.Range("Add1")
s3 = ActiveSheet.Range("Add2")
appWD.Visible = True
appWD.Documents.Open Filename:="C:UsersRemy LabueDocumentsCareCore WorkTest Letter TemplatesTest Letter 1.DOCX"
Call DoFindReplace(FindText:="(1)", ReplaceText:=s1)...................
I have two worksheets in two separate workbooks. One contains only two columns - a list of codes and the corresponding name of an institution. The other workbook contains a column with the codes. I want to replace the codes in the second workbook with the names from the first workbook.
I found this code on [url] here: Find & Replace macro
Sub DoIt()
Dim rRange As Range, rCell As Range
On Error Resume Next
With Workbooks("Personal.xls")
Set rRange = .Sheets(1).Range("A1", .Sheet1.Range("B65536").End(xlUp))
End With
For Each rCell In rRange.Columns(1).Cells
Selection.Replace What:=rCell, Replacement:=rCell(1, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next rCell
On Error Resume Next
End Sub
This is exactly what I need, but like the last person who asked about it, I can't get it to work. I've gone through Dave's instructions a number of times, and now I'm stumped.
I have a excel which contains 5 columns in which 5th column data cells has to be replaced with another set of data which have relationship with other 4 columns data.
View 3 Replies View RelatedI have list of email ids and some of which contains accented characters.I need to replace these accented characters with regular character.
Example:
rian.pieda@intt.com.cn
ario.sancez@acca.com
I want to have a macro that will look at a few cells say A1 & A2 and then use the contents of the cells say 1Q07 & 2Q07 respectively to do a find and replace find 1Q07 and replace it with 2Q07. I want it to use the contents of the cell and not just the text because the contents of the cell will change each time used. I tride to do it here but I dont have it yet. It doesnt seem to pass the value to the variable and the variable doesnt seem to do the find and replace.
Private Sub Update_Click()
Application. ScreenUpdating = False
Dim quarter As String
quarter = CStr(txtquarter.Text)
txtquarter.Text = CStr(quarter)
Sheets("Income Statement").Select
Dim oneq As Single
Dim twoq As Single
Dim threeq As Single
Dim fourq As Single
Dim fiveq As Single
oneq = ActiveSheet.Range("A2").Value
twoq = oneq - 1
threeq = twoq - 1
fourq = oneq - 4
fiveq = oneq - 5
how to write macros. Here is what i need:
-I need the macro to only search within column "B"
-There is both a date and time within the cell, the time is always " 00:00:00" and I need it to be replaced with blank/ ""
- The date is represented "1/1/2013" and i need the "/" to be turned into "-"
- and i need the number format to be changed to a custom format of "m-d-yyyy"
I am trying to make a find and replace macro for multiple items.
Please see the attached file for further explanation : macro.xls
After much fretting over this (and many other Excel issues), I've decided to seek out some assistance. I've included an example with columns, because I'm not very fluent in Excel terminology.
View 5 Replies View RelatedI 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'm looking for macro for find and replace characters in a locked sheet, if a cell contains for example Monday (First day of week)
I want with a command or macro to change the (First day of week) in (Top of the Week) like this Monday (Top of the Week)
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.
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 RelatedI 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 RelatedI 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 RelatedSee 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] .........
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?
[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?
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 RelatedI 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 RelatedRange B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.
Example
Find all occurences of And or AND and replace with and
Find all occurences of With or WITH and replace with with.
Find all occurences of Or or OR and replace with or