Macro to Find and Replace in a loop for a long list of rows
Hello - I am trying to build a Find and Replace macro that allows a list of find (Sheet2!Column A) and Replace (Sheet2!Column B) alphanumeric strings to be applied to an input List Sheet1:Column A). Each Find and Replace row in sheet2 should be applied to ALL of Column A in sheet 1.
The standard find and replace function in Excel does not work because the text strings we are trying to use to replace the information have a few too many characters. May need to use the Search/Substitute functions to accomplish it. To say it another way, I want to apply a Find and Replace for each row in the Sheet2 (row 1, then row 2, then row 3, etc) to all rows in the Sheet1. So the find and replace function will be applied to every single row in Sheet1. I am happy to send over a sample file if it helps. If I am confusing you, just ask and am happy to clarify. FYI, this will be using a computer with Excel 2010 |
Macro to Find and Replace in a loop for a long list of rows
Hello - I am trying to build a Find and Replace macro that allows a list of
find (Sheet2!Column A) and Replace (Sheet2!Column B) alphanumeric strings to be applied to an input List Sheet1:Column A). Each Find and Replace row in sheet2 should be applied to ALL of Column A in sheet 1. The standard find and replace function in Excel does not work because the text strings we are trying to use to replace the information have a few too many characters. May need to use the Search/Substitute functions to accomplish it. To say it another way, I want to apply a Find and Replace for each row in the Sheet2 (row 1, then row 2, then row 3, etc) to all rows in the Sheet1. So the find and replace function will be applied to every single row in Sheet1. I am happy to send over a sample file if it helps. If I am confusing you, just ask and am happy to clarify. FYI, this will be using a computer with Excel 2010 So is this what you mean? For Each Row in Sheet1!ColA 'Loop thru each row in Sheet2!ColA For Each Row in Sheet2!ColA If Find Sheet2!ColA in Sheet1!ColA Then 'Replace w/Sheet2!ColB Replace w/Sheet2!ColumnA.Offset(0, 1) End If Next Row in Sheet2!ColA Next Row in Sheet1!ColA If so then assuming contiguous data in both sheets: Sub FindReplaceStrings() Dim vSrc, vTgt, sRng$, lLastRow&, n&, j& 'Load the ranges into arrays With ActiveWorkbook.Sheets("Sheet1") lLastRow = .UsedRange.Rows.Count: sRng = "A1:A" & CStr(lLastRow) vTgt = .Range(sRng) End With With ActiveWorkbook.Sheets("Sheet2") lLastRow = .UsedRange.Rows.Count: sRng = "A1:B" & CStr(lLastRow) vSrc = .Range(sRng) End With 'Do Find/Replace For n = LBound(vTgt) To UBound(vTgt) For j = LBound(vSrc) To UBound(vSrc) vTgt(n, 1) = Replace(vTgt(n, 1), vSrc(j, 1), vSrc(j, 2)) Next 'j Next 'n 'Reload the range ActiveWorkbook.Sheets("Sheet1").Range("A1").Resize (UBound(vTgt)) = vTgt End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
All times are GMT +1. The time now is 01:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com