Find & Replace Macro
Sub ABC()
Dim sh as worksheet, sh1 as worksheet
Dim rng as Range, cell as Range
set sh = Activesheet
set rng = sh.Range(sh.Cells(2,1),sh.Cells(2,1).End(xldown))
for each cell in rng
for each sh1 in worksheets
sh1.cells.Replace What:=cell.offset(0,1).Value, _
Replacement:=cell.Value, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next sh1
Next cell
End Sub
Assume that there will be no duplicate values between columns
Untested pseudo code, but should get you started.
--
Regards,
Tom Ogilvy
"Rob Wnuk" wrote:
Here is my data:
Col 1 Col 2
STDNUMBER IDSTUDENT
27050 1782
27004 1784
27063 1786
27006 1788
Basically what I need to do is look in the entire spreadsheet for (in
example 1) 1782 and replace it with 27050, and continue down the list
automatically to example 2, find 1784 and replace with 27004 and so on until
it runs out of data in these 2 columns.
Can anyone help????
Thanks
|