Monday, May 14, 2007

Match Excel Column against a Standard Range

Replace Values of a Column when it Matches

If you want to match the names in column2 against the standard names in column 1 & replace by the standard names the following should help:

Sub Replace_TExt()

For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCellTypeLastCell).Row

If Trim(ActiveSheet.Range("B" & i)) <> "" Then

ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

End If

Next i
End Sub

No comments:

Post a Comment

Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.