Need find \ replace macro?
Posted by travis on January 10, 2002 12:14 PM
I have two columns of data. I want to search column A for a specific value (X). When I find that value, I want to change the data in the same row but in column B to a different value (y). For example, when I find the number 22 anywhere in column A, I want the data in the same row, column B to be "hello". Any help would be great. Thanks!
Posted by Cinna on January 10, 2002 1:58 PM
Try this :-
Dim rng As Range
Set rng = Range([A1], [A65536].End(xlUp))
Application.ScreenUpdating = False
Columns(3).Insert
With rng.Offset(0, 2)
.FormulaR1C1 = "=IF(RC[-2]=22,""hello"",RC[-1])"
.Copy
.PasteSpecial Paste:=xlValues
End With
Columns(2).Delete
Posted by Jacob on January 10, 2002 1:59 PM
Hi
Sub ReplaceIt()
with sheets("sheet1").range("A1:A100")
set c = .find(what:="22", lookin:=xlvalues, lookat:=xlwhole)
firstaddress = c.address
if not c is nothing
do
c.address.offset(0,1).formulaR1C1 = "Hello"
set c = .findnext(c)
loop while not c is nothing and c.address<>firstaddress
else
end if
end with
End Sub
HTH
Jacob
