|
Find and replace
I receive data once a day into an Excel spreadsheet and need to convert 26 2
digit different labels to words. For example RD needs to be converted to READING. I am using find and replace but the data does not save for the next day. Is there any way that I can create 26 templates that I can then use without having to type the full words every day. |
Find and replace
One way is to use a sub posted by Gary''s Student [Sub transla() below] which
can do all of your 26 multiple find n replace at one go Here's the easy steps to implement: (Try on a spare copy of your file) Install GS' sub into a regular module as below: In your excel file: 1. Press Alt+F11 to go to VBE In VBE, click Insert Module Copy n paste GS' sub (below) into the code window (everything within the dotted lines) '---------- Sub transla() 'GS .newusers Dim inn() As String, outt() As String Sheets("xlator").Activate n = Cells(Rows.Count, 1).End(xlUp).Row ReDim inn(1 To n), outt(1 To n) For i = 1 To n inn(i) = Cells(i, 1).Value outt(i) = Cells(i, 2).Value Next Sheets("Sheet2").Activate For Each r In ActiveSheet.UsedRange v = r.Value For i = 1 To n v = Replace(v, inn(i), outt(i)) Next r.Value = v Next End Sub '--------- Press Alt+Q to get back to Excel 2. In Excel, a. Insert a new sheet, name it as: xlator List the text that you want to FIND in col A (ie list your 26 2 digit labels) List the corresponding full text to REPLACE it with in col B b. Go to the sheet that you have the text to be found n replaced all at one go (this text could be all over the place within the sheet) Rename the sheet as: Sheet2 Alternatively, you could go back to VBE and amend this line in the sub to reflect your actual sheetname: Sheets("Sheet2").Activate c. Press Alt+F8 to bring up the Macro dialog Either double-click directly on "transla" inside the window, or select "transla", click Run, to run the sub. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "alistairp" wrote: I receive data once a day into an Excel spreadsheet and need to convert 26 2 digit different labels to words. For example RD needs to be converted to READING. I am using find and replace but the data does not save for the next day. Is there any way that I can create 26 templates that I can then use without having to type the full words every day. |
Find and replace
Thanks Max
This works until the final step when all data within the worksheet is being amended. I need to Macro to say something similar to the tick box in Find & Replace that says 'Match entire cell contents' only. For example I am amending OS to read OXFORD STREET. OS is in Column A and despite highlighting this column when I click Run all text in the spreadsheet is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then it is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC. Presumably I have missed something. Any further assistance would be appreciated. Alistair "Max" wrote: One way is to use a sub posted by Gary''s Student [Sub transla() below] which can do all of your 26 multiple find n replace at one go Here's the easy steps to implement: (Try on a spare copy of your file) Install GS' sub into a regular module as below: In your excel file: 1. Press Alt+F11 to go to VBE In VBE, click Insert Module Copy n paste GS' sub (below) into the code window (everything within the dotted lines) '---------- Sub transla() 'GS .newusers Dim inn() As String, outt() As String Sheets("xlator").Activate n = Cells(Rows.Count, 1).End(xlUp).Row ReDim inn(1 To n), outt(1 To n) For i = 1 To n inn(i) = Cells(i, 1).Value outt(i) = Cells(i, 2).Value Next Sheets("Sheet2").Activate For Each r In ActiveSheet.UsedRange v = r.Value For i = 1 To n v = Replace(v, inn(i), outt(i)) Next r.Value = v Next End Sub '--------- Press Alt+Q to get back to Excel 2. In Excel, a. Insert a new sheet, name it as: xlator List the text that you want to FIND in col A (ie list your 26 2 digit labels) List the corresponding full text to REPLACE it with in col B b. Go to the sheet that you have the text to be found n replaced all at one go (this text could be all over the place within the sheet) Rename the sheet as: Sheet2 Alternatively, you could go back to VBE and amend this line in the sub to reflect your actual sheetname: Sheets("Sheet2").Activate c. Press Alt+F8 to bring up the Macro dialog Either double-click directly on "transla" inside the window, or select "transla", click Run, to run the sub. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "alistairp" wrote: I receive data once a day into an Excel spreadsheet and need to convert 26 2 digit different labels to words. For example RD needs to be converted to READING. I am using find and replace but the data does not save for the next day. Is there any way that I can create 26 templates that I can then use without having to type the full words every day. |
Find and replace
Perhaps a simpler process using vlookup would suffice?
Reference list is assumed in sheet: xlator, cols A and B where col A = items to find, col B = what to replace it with With source data assumed in A2 down in the other sheet Put in B2: =IF(A2="","",IF(ISNA(VLOOKUP(A2,xlator!A:B,2,0)),A 2,VLOOKUP(A2,xlator!A:B,2,0))) Copy B2 down to the last row of data in col A. Then copy col B, overwrite col A with a paste special as values. Delete col B. If you want to pursue the sub approach, try a post in .programming. Or hang around here awhile, maybe other responders versed in vba might chime in. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "alistairp" wrote: Thanks Max This works until the final step when all data within the worksheet is being amended. I need to Macro to say something similar to the tick box in Find & Replace that says 'Match entire cell contents' only. For example I am amending OS to read OXFORD STREET. OS is in Column A and despite highlighting this column when I click Run all text in the spreadsheet is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then it is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC. Presumably I have missed something. Any further assistance would be appreciated. Alistair |
Find and replace
Hi
The minor modification of the code worked fine for me Sub transla() 'GS .newusers Dim inn() As String, outt() As String Dim n As Long, i As Long, r As Range, v As Variant Sheets("xlator").Activate n = Cells(Rows.Count, 1).End(xlUp).Row ReDim inn(1 To n), outt(1 To n) For i = 1 To n inn(i) = Cells(i, 1).Value outt(i) = Cells(i, 2).Value Next Sheets("Sheet2").Activate For Each r In ActiveSheet.UsedRange v = r.Value For i = 1 To n v = Replace(v, inn(i), outt(i)) Next r.Value = v Next End Sub -- Regards Roger Govier "alistairp" wrote in message ... Thanks Max This works until the final step when all data within the worksheet is being amended. I need to Macro to say something similar to the tick box in Find & Replace that says 'Match entire cell contents' only. For example I am amending OS to read OXFORD STREET. OS is in Column A and despite highlighting this column when I click Run all text in the spreadsheet is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then it is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC. Presumably I have missed something. Any further assistance would be appreciated. Alistair "Max" wrote: One way is to use a sub posted by Gary''s Student [Sub transla() below] which can do all of your 26 multiple find n replace at one go Here's the easy steps to implement: (Try on a spare copy of your file) Install GS' sub into a regular module as below: In your excel file: 1. Press Alt+F11 to go to VBE In VBE, click Insert Module Copy n paste GS' sub (below) into the code window (everything within the dotted lines) '---------- Sub transla() 'GS .newusers Dim inn() As String, outt() As String Sheets("xlator").Activate n = Cells(Rows.Count, 1).End(xlUp).Row ReDim inn(1 To n), outt(1 To n) For i = 1 To n inn(i) = Cells(i, 1).Value outt(i) = Cells(i, 2).Value Next Sheets("Sheet2").Activate For Each r In ActiveSheet.UsedRange v = r.Value For i = 1 To n v = Replace(v, inn(i), outt(i)) Next r.Value = v Next End Sub '--------- Press Alt+Q to get back to Excel 2. In Excel, a. Insert a new sheet, name it as: xlator List the text that you want to FIND in col A (ie list your 26 2 digit labels) List the corresponding full text to REPLACE it with in col B b. Go to the sheet that you have the text to be found n replaced all at one go (this text could be all over the place within the sheet) Rename the sheet as: Sheet2 Alternatively, you could go back to VBE and amend this line in the sub to reflect your actual sheetname: Sheets("Sheet2").Activate c. Press Alt+F8 to bring up the Macro dialog Either double-click directly on "transla" inside the window, or select "transla", click Run, to run the sub. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "alistairp" wrote: I receive data once a day into an Excel spreadsheet and need to convert 26 2 digit different labels to words. For example RD needs to be converted to READING. I am using find and replace but the data does not save for the next day. Is there any way that I can create 26 templates that I can then use without having to type the full words every day. |
Find and replace
vlookup does the trick. Many thanks for your help.
Alistair "Roger Govier" wrote: Hi The minor modification of the code worked fine for me Sub transla() 'GS .newusers Dim inn() As String, outt() As String Dim n As Long, i As Long, r As Range, v As Variant Sheets("xlator").Activate n = Cells(Rows.Count, 1).End(xlUp).Row ReDim inn(1 To n), outt(1 To n) For i = 1 To n inn(i) = Cells(i, 1).Value outt(i) = Cells(i, 2).Value Next Sheets("Sheet2").Activate For Each r In ActiveSheet.UsedRange v = r.Value For i = 1 To n v = Replace(v, inn(i), outt(i)) Next r.Value = v Next End Sub -- Regards Roger Govier "alistairp" wrote in message ... Thanks Max This works until the final step when all data within the worksheet is being amended. I need to Macro to say something similar to the tick box in Find & Replace that says 'Match entire cell contents' only. For example I am amending OS to read OXFORD STREET. OS is in Column A and despite highlighting this column when I click Run all text in the spreadsheet is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then it is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC. Presumably I have missed something. Any further assistance would be appreciated. Alistair "Max" wrote: One way is to use a sub posted by Gary''s Student [Sub transla() below] which can do all of your 26 multiple find n replace at one go Here's the easy steps to implement: (Try on a spare copy of your file) Install GS' sub into a regular module as below: In your excel file: 1. Press Alt+F11 to go to VBE In VBE, click Insert Module Copy n paste GS' sub (below) into the code window (everything within the dotted lines) '---------- Sub transla() 'GS .newusers Dim inn() As String, outt() As String Sheets("xlator").Activate n = Cells(Rows.Count, 1).End(xlUp).Row ReDim inn(1 To n), outt(1 To n) For i = 1 To n inn(i) = Cells(i, 1).Value outt(i) = Cells(i, 2).Value Next Sheets("Sheet2").Activate For Each r In ActiveSheet.UsedRange v = r.Value For i = 1 To n v = Replace(v, inn(i), outt(i)) Next r.Value = v Next End Sub '--------- Press Alt+Q to get back to Excel 2. In Excel, a. Insert a new sheet, name it as: xlator List the text that you want to FIND in col A (ie list your 26 2 digit labels) List the corresponding full text to REPLACE it with in col B b. Go to the sheet that you have the text to be found n replaced all at one go (this text could be all over the place within the sheet) Rename the sheet as: Sheet2 Alternatively, you could go back to VBE and amend this line in the sub to reflect your actual sheetname: Sheets("Sheet2").Activate c. Press Alt+F8 to bring up the Macro dialog Either double-click directly on "transla" inside the window, or select "transla", click Run, to run the sub. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "alistairp" wrote: I receive data once a day into an Excel spreadsheet and need to convert 26 2 digit different labels to words. For example RD needs to be converted to READING. I am using find and replace but the data does not save for the next day. Is there any way that I can create 26 templates that I can then use without having to type the full words every day. |
Find and replace
vlookup does the trick. Many thanks for your help.
Alistair "Max" wrote: Perhaps a simpler process using vlookup would suffice? Reference list is assumed in sheet: xlator, cols A and B where col A = items to find, col B = what to replace it with With source data assumed in A2 down in the other sheet Put in B2: =IF(A2="","",IF(ISNA(VLOOKUP(A2,xlator!A:B,2,0)),A 2,VLOOKUP(A2,xlator!A:B,2,0))) Copy B2 down to the last row of data in col A. Then copy col B, overwrite col A with a paste special as values. Delete col B. If you want to pursue the sub approach, try a post in .programming. Or hang around here awhile, maybe other responders versed in vba might chime in. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "alistairp" wrote: Thanks Max This works until the final step when all data within the worksheet is being amended. I need to Macro to say something similar to the tick box in Find & Replace that says 'Match entire cell contents' only. For example I am amending OS to read OXFORD STREET. OS is in Column A and despite highlighting this column when I click Run all text in the spreadsheet is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then it is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC. Presumably I have missed something. Any further assistance would be appreciated. Alistair |
Find and replace
Good to hear it did.
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "alistairp" wrote in message ... vlookup does the trick. Many thanks for your help. Alistair |
| All times are GMT +1. The time now is 01:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com