Find and Replace 06-02-10

Hi,
I am after a piece of code that can replace words from one tab to the next
For example:
Sheet1 - pick up the first word from the Wrong Column(“Wrong1”) to find and 
replace that word in Sheet2 with the correct word found in the Correct column 
in Sheet1(“Correct 1”)
Then keep going until it reaches the last word within the Wrong column of 
Sheet1

Wrong Column	Correct Column
Wrong 1	Correct 1
Wrong 2	Correct 2
Wrong 3	Correct 3
Wrong 4	Correct 4
Wrong 5	Correct 5
Wrong 6	Correct 6
Wrong 7	Correct 7

My goodness, I hope that makes sense

Help greatly appreciated

Regards
John 

0
Utf
6/2/2010 3:44:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
989 Views

Similar Articles

[PageSpeed] 8

Not really
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"JohnUK" <JohnUK@discussions.microsoft.com> wrote in message 
news:59E538C6-3C5D-40FA-9E75-6598228CD403@microsoft.com...
> Hi,
> I am after a piece of code that can replace words from one tab to the next
> For example:
> Sheet1 - pick up the first word from the Wrong Column(“Wrong1”) to find 
> and
> replace that word in Sheet2 with the correct word found in the Correct 
> column
> in Sheet1(“Correct 1”)
> Then keep going until it reaches the last word within the Wrong column of
> Sheet1
>
> Wrong Column Correct Column
> Wrong 1 Correct 1
> Wrong 2 Correct 2
> Wrong 3 Correct 3
> Wrong 4 Correct 4
> Wrong 5 Correct 5
> Wrong 6 Correct 6
> Wrong 7 Correct 7
>
> My goodness, I hope that makes sense
>
> Help greatly appreciated
>
> Regards
> John
> 

0
Don
6/2/2010 3:56:09 PM
Hope this will point you in the right direction...

Sub FindandReplace()
Dim ws1 As Worksheet, ws2 As Worksheet, lngRow As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

For lngRow = 1 To ws1.Cells(Rows.Count, "B").End(xlUp).Row
If Trim(ws1.Range("B" & lngRow)) <> "" And _
Trim(ws1.Range("A" & lngRow)) <> "" Then _
ws2.Cells.Replace What:=ws1.Range("A" & lngRow), _
Replacement:=ws1.Range("B" & lngRow), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub


-- 
Jacob (MVP - Excel)


"JohnUK" wrote:

> Hi,
> I am after a piece of code that can replace words from one tab to the next
> For example:
> Sheet1 - pick up the first word from the Wrong Column(“Wrong1”) to find and 
> replace that word in Sheet2 with the correct word found in the Correct column 
> in Sheet1(“Correct 1”)
> Then keep going until it reaches the last word within the Wrong column of 
> Sheet1
> 
> Wrong Column	Correct Column
> Wrong 1	Correct 1
> Wrong 2	Correct 2
> Wrong 3	Correct 3
> Wrong 4	Correct 4
> Wrong 5	Correct 5
> Wrong 6	Correct 6
> Wrong 7	Correct 7
> 
> My goodness, I hope that makes sense
> 
> Help greatly appreciated
> 
> Regards
> John 
> 
0
Utf
6/2/2010 4:30:01 PM
Many thanks Jacab
Fantastic!! Exactly what I wanted
Kind Regards
John

"Jacob Skaria" wrote:

> Hope this will point you in the right direction...
> 
> Sub FindandReplace()
> Dim ws1 As Worksheet, ws2 As Worksheet, lngRow As Long
> 
> Set ws1 = Worksheets("Sheet1")
> Set ws2 = Worksheets("Sheet2")
> 
> For lngRow = 1 To ws1.Cells(Rows.Count, "B").End(xlUp).Row
> If Trim(ws1.Range("B" & lngRow)) <> "" And _
> Trim(ws1.Range("A" & lngRow)) <> "" Then _
> ws2.Cells.Replace What:=ws1.Range("A" & lngRow), _
> Replacement:=ws1.Range("B" & lngRow), LookAt:=xlWhole, _
> SearchOrder:=xlByRows, MatchCase:=False
> Next
> 
> End Sub
> 
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "JohnUK" wrote:
> 
> > Hi,
> > I am after a piece of code that can replace words from one tab to the next
> > For example:
> > Sheet1 - pick up the first word from the Wrong Column(“Wrong1”) to find and 
> > replace that word in Sheet2 with the correct word found in the Correct column 
> > in Sheet1(“Correct 1”)
> > Then keep going until it reaches the last word within the Wrong column of 
> > Sheet1
> > 
> > Wrong Column	Correct Column
> > Wrong 1	Correct 1
> > Wrong 2	Correct 2
> > Wrong 3	Correct 3
> > Wrong 4	Correct 4
> > Wrong 5	Correct 5
> > Wrong 6	Correct 6
> > Wrong 7	Correct 7
> > 
> > My goodness, I hope that makes sense
> > 
> > Help greatly appreciated
> > 
> > Regards
> > John 
> > 
0
Utf
6/3/2010 6:42:41 AM
Thanks for offerering your help Don, but Jacob had just what I wanted
Regards
John

"Don Guillett" wrote:

> Not really
>       If desired, send your file to my address below. I will only look if:
>       1. You send a copy of this message on an inserted sheet
>       2. You give me the newsgroup and the subject line
>       3. You send a clear explanation of what you want
>       4. You send before/after examples and expected results.
> 
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "JohnUK" <JohnUK@discussions.microsoft.com> wrote in message 
> news:59E538C6-3C5D-40FA-9E75-6598228CD403@microsoft.com...
> > Hi,
> > I am after a piece of code that can replace words from one tab to the next
> > For example:
> > Sheet1 - pick up the first word from the Wrong Column(“Wrong1”) to find 
> > and
> > replace that word in Sheet2 with the correct word found in the Correct 
> > column
> > in Sheet1(“Correct 1”)
> > Then keep going until it reaches the last word within the Wrong column of
> > Sheet1
> >
> > Wrong Column Correct Column
> > Wrong 1 Correct 1
> > Wrong 2 Correct 2
> > Wrong 3 Correct 3
> > Wrong 4 Correct 4
> > Wrong 5 Correct 5
> > Wrong 6 Correct 6
> > Wrong 7 Correct 7
> >
> > My goodness, I hope that makes sense
> >
> > Help greatly appreciated
> >
> > Regards
> > John
> > 
> 
> .
> 
0
Utf
6/3/2010 7:08:15 AM
Reply:

Similar Artilces:

Windows mail problem 07-16-10
Tried to log into my optonline account and received the following message: The connection to the server has failed. Account: 'mail.optonline.net', Server: 'mail.optonline.net', Protocol: POP3, Port: 995, Secure(SSL): Yes, Socket Error: 10060, Error Number: 0x800CCC0E Can anyone decipher this for me? What is the issue? You should not be using advanced ports or SSL, see http://optimum.custhelp.com/cgi-bin/optimum.cfg/php/enduser/std_adp.php?p_faqid=2397&p_created=1237397986&p_sid=ZbGnw*4k&p_accessibility=0&p_redirect=&p_lva=&p_sp=cF9zcmNoPSZ...

crystal reports 9.2.2 02-03-04
I have been working on customizing reports. After I upgraded crm to 1.2 I can no longer access my reports. I tried to install the crystal repors enhancement for 1.2. I am getting an error that I am using crystal reports 9.2.0 and I need to upgrade to 9.2.2? I had crystal reports 9.0 professional edition installed on my computer also. I removed that and then tried to install the crm 1.2 crystal reports enhancement and I am getting the same errors. Please help. Thank you It seems that CRM requires a very specific version of crystal reports ie 9.2.2 Microsoft have told me that you ...

RESOURCE USAGE 03-21-10
I work with a calendar that is setting 9 hours work/day for all the resources. In the resource usage I see that more than 8 hours of work is shown in red, but also sometimes all kinds of hours like 4.5, 5.6, 4.8 hs is red as well, even though is a normal working day. What is going on here???? txs A couple of things: 1) Are you sure the resource calendars are also set to 9 hours per day? If not, they would show up overallocated when assigned to 9 hours of tasks. 2) Project calculates overallocations to the minute. If you have two tasks, both assigned to the same resource...

Windows installer problem 04-08-10
Hi All, After login to windows xp, on screen displayed windows installer and nothing showing on screen .Any reply will be appreciated. Best regards Anji Always state your full Windows version (e.g., WinXP SP3; WinXP 64-bit SP2) when posting in a forum or newsgroup. Please do so in your next reply. Was the computer fully patched at Windows Update when this problem started? What anti-virus application or security suite is installed and is your subscription current? What anti-spyware applications (other than Defender)? What third-party firewall (if any)? Has a(another)...

Parsing thru columns to find the last number
I have six columns of data and am unsure how to get to the final replacement of an item. See, col A is the itemcode, then b,c,d,e,f contain item codes that have replaced the col a item. Not all items have been replaced five times, some none, some only two, in this case col b would be blank and or col C would be blank. My goal is to find the last item in the columns that were replaced and put it in a column on it's own. So the outcome will be colA is replaced by Col G. The data looks like this. A B C D E F G z k o p i So this was replaced four times and the end res...

$10,000 a week, as much as $40,000 a month or even up to $500,000 a year!
1) Though multi-level Marketing has been near and dear to many for more than 50 years, 95% of those who trusted it have failed. 2) They failed because most people simply cannot close sales. Without closing they cannot recruit. 3) Without recruiting, duplication cannot happen. Even the 5% who CAN recruit cannot achieve their full potential without duplication. 4) None of the hype, recruiting systems, replicated websites, interruption marketing, sexy products, exotic compensation plans, training kits, books, tapes, seminars, conference calls, or "heavy hitters" have been able to...

Can't Find, Re-subscribe or subscribe to any Excel NG's
I'm running Vista Home Premium SP2, 2 GB RAM, Firefox, Spyware Doctor (with real-time Intelliscan), MBAM, SAS and Cleaner, Office 2007. I might be posting on the wrong NG but that's because I cannot seem to subscribe anymore to any Excel NG's such as: Microsoft. public.excel.anythingatall, so I can't set up a new post. Each of these NG's related to Excel is declaring that there are "no posts available anymore". I used to be subscribed to Microsoft. public.excel.setup, Microsoft. public.excel.programming, Microsoft. public.excel.general, and a few...

Outlook 2002 Advanced Find
When I use Advanced Find in Outlook 2002 to locate mail I've archived in a personal folder, I would like to be able to see the folder hierarchy where the mail item was found. Currently, I can only see the lowest level folder, using the "In Folder" column. Is there a way to get this information? ...

Find repeat and than merge and centre
Hi! How to find repeated data and then merge and centre. My data is from A to M.Range from A1 to M4500 Any Function Or Macro. Thanks Hardeep Hi, I think we need more detail - what do you want to merge and center, multiple rows, multiple columns, multiple blocks of cells? You will loose the data in all but one of the merged cells, Excel does not merge the data. What determines if a range has repeat data - does the data in two cells above each other need to be the same, does all the data on two adjacent rows need to be the same, do the rows with duplicate data need to be adjacent? -...

Formula Help 02-02-10
How do I enter a formula to look at a number and based on calculation round up or down to nearest hundreth? For example 421 would round down to 400 while 573 would round up to 600. try =round(a1,-2) where A1 has the number you want to round to the 100's "Jackie Morin" wrote: > How do I enter a formula to look at a number and based on calculation round > up or down to nearest hundreth? For example 421 would round down to 400 while > 573 would round up to 600. You could look up ROUND in Excel help. Try =ROUND(A2,-2) -- David Biddulph Jackie Morin wr...

Exchange 5.5 #10
I followed the article suggested and identified the last committed log. When I go to the transaction log folder, however, there are no back logs to move. The only logs in the folder are edb.log, edb0264D.log (last known committed log), res1.log and res2.log. The article says not to move the edb.log, res1.log or res2.log, just move any logs older than the last one committed, but there aren't any. Any more suggestions? Thanks! ...

Find cell numbers in a table so I can multiply
Hello, I have a word document and in the documents header there is a table. This table a has diferent number of cells in each row like: Row one: 2 cells Row two: 4 cells Row three: 2 cells Row four: 10 cells Row five: 2 cells What I wanted to do is multipy 3 cells together thats in row four and show the total in the same row. I have a number in the 3rd cell that needs * by the number in the fith cell that needs * by the number in the seventh cell and totaled in the 9th cell. I know I have to select the ninth cell and select table/formula then what? -- Thanks, Chad I ...

HELP! 12-06-07
Excel won't open and I've both tried re-installing it, and rebooting what should I do? On Thu, 6 Dec 2007 00:42:20 -0500, avarage wrote: > Excel won't open and I've both tried re-installing it, and rebooting what > should I do? You have posted this message to the wrong newsgroup. The access in this groups name refers to Microsoft Access, a database program. Please repost to the correct newsgroup for whatever Office program you are using. I would suggest you include your Windows and Office version number in the message. -- Fred Please respond only to this newsgrou...

excel sorting 03-28-10
I have a text spreadsheet of 5,000 names and addresses. Column B needs to restricted to only 16 characters in that column. How do I send the records to the bottom of the page (or delete them) that have data that exceeds 16 characters in Column B? You could insert a new column. In that column, in row 2 put the formula =len(B2) and copy it down the column. Then sort the table on then new column ascending. Tom ...

replace manual page breaks WITH section breaks
Hello everyone -- There is no problem searching FOR section breaks and replacing them WITH something else, but there seems to be no way to do the reverse -- there is no Section Break choice in the Replace With pop-up window. Typing in ^b just produces an error. Is there a way to do this? Thank you all, -Lynne Please ignore question, folks. I just found a workaround here: http://support.microsoft.com/kb/136260 -Lynne Here it "Elessvie" wrote: > Hello everyone -- There is no problem searching FOR section breaks and > replacing them WITH somethin...

SendObject 02-03-08
I have a routine that creates a list of e-mails to be sent out, when the list is completed it then loops through and uses the Docmd.sendObject to send each mail (Access 2000). This used to work very well and I have not had any problem with it until about 3 months ago when it started to send the first mail all ok, out look would ask if it was ok to send and you said yes and it was sent and so on, but now it sends the first mail all ok and then it just loops through the rest with no outlook messages or error messages but dos not send any of the other mails. This used to work very well abo...

Option Buttons 06-21-07
If I choose a Control Source I can get the option buttons to work. Can you help? -- Dick Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200706/1 Do you want us to push the button? What is the problem? UpRider "richardlafrance via AccessMonster.com" <u31432@uwe> wrote in message news:740b97593dc91@uwe... > If I choose a Control Source I can get the option buttons to work. Can > you > help? > > -- > Dick > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/ac...

permissions 03-29-06
I have a Marketing Professional who needs the ability to convert Leads - what circle / state do I need to fill out in the Security role? To Disqualify a lead -------------------- 1. Read Lead 2. Write Lead To Qualify a lead to an opportunity ---------------------------------- 1. Read Lead 2. Read account 3. Read contact 4. Read opportunity 5. Create opportunity 6. Write Lead 7. AppendTo lead 8. AppendTo account 9. AppendTo contact 10. Append opportunity To Qualify a lead to an account ------------------------------ 1. Read Lead 2. Read account 3. Write Lead 4. AppendTo lead 5. Append Acco...

ploting one cell from 10 worksheets into one chart
i am trying to figure out a way to plot one cell, lets say c-1 from 10 -30 worksheets, in one workbook; on one chart. Each worksheet is a date and each c-1 value is just a number. any help would help thanks alex n. alex.nazarchuK@unitedwater.com ------------------------------------------------ Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/ -- View and post usenet messages directly from http://www.ExcelTip.com -- Hundreds of free MS Excel tips, tricks and solutions ------------------------------------------------ You can plot each data point as a separate ser...

Macro to delete rows 03-17-10
I'd appreciate some help in creating a macro to delete rows. Here's what I need: 1) Delete rows 1-13 2) Then find all rows that are highlited blue and delete them 3) Then find all rows that include the text "Distr" and delete them Suggestions? Thanks in advance. Tell us how the rows came to be blue... -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "JeffF" <JeffF@discussions.microsoft.com> wrote in message news:863D165C-D100-42E5-9294-3569F1829DF0@microsoft.com... > I'd appreciate some help in creating a...

Command Button Wizard 02-14-08
Using Access 2000, I have built an extensive database, & the command buttons built with the Wizard all work. All simple commands, such as Print, Find Record, etc. I build another database (ostensibly with all the same settings) and the command buttons built with the Wizard will not work - none of them - when clicking on such a button, it depresses, but nothing happens. I build another database, and the command buttons built with the Wizard all work. Why? What variable is available that apparently switches off all the buttons? The macro code established by the Wizard in a db th...

Tennessee Frist Sales Tax Holiday 8/4/06 to 8/6/06?
Hello, Today I got a memo from the Tennessee Department of Revenue for a Back-to-School Supplies, Clothing, and Computers Tax-Exempt During Tennessee's First Sales Tax Holiday. In August of this year, Tennessee will hold its first annual sales tax holiday, giving shoppers the opportunity to purchase certian school suppliers, computers and clothing tax-free for a specific period of time beginning at 12:01am on Friday, Aug 4 and ending at 11:59pl on Sunday, Aug 6. We have stores on all different parts of Tennessee with different items sales tax base on their location. I saw two opt...

How do I autodate(excel) every 7 days ex. 1/1/06-1/31/06?
Enter 1/1/2006 in A1 In A2 enter =A1+7 Drag/copy down. Gord Dibben MS Excel MVP On Thu, 12 Jan 2006 11:36:03 -0800, "tlk1214" <tlk1214@discussions.microsoft.com> wrote: ...

Stats microsoft.public.windowsxp.general (last 7 days) 06-07-10
"Caveat: Quantity is not necessarily a measure of Quality" Newsgroup.................: microsoft.public.windowsxp.general Stats Were Taken..........: Mon, 07 Jun 2010 08:14:28 GMT Stats Begin...............: Mon, 31 May 2010 08:29:11 GMT Stats End.................: Mon, 07 Jun 2010 06:42:42 GMT Days......................: 7 Total No. of Articles.....: 526 Total No. of Characters...: 1263268 Total Volume..............: 1233 Messages Per Day..........: 75.1 Characters Per Day........: 180466.9 Average Daily Volume......: 176 kB Total Posters This Week...:...

Windows Update error 80070103 01-28-10
I installed Creative X-Fi Titaniun audio sound card on my Destop Computer. The drivers on cd that came with this sound card for Vista and XP. I am using Windows 7 and having trouble installing the driver manually. When Windows Update trys to download it, but stops and give me error message "80070103" everytime. Why is it give me error message when there is no driver installed on this computer? Obtain the appropriate driver update (if needed) from the device manufacturer's download page, not Windows Update. TJBuss wrote: > I installed Creative X-Fi Titaniun a...