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

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