Find & Replace Macro

[Guest]

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

 

[Guest]

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.

 

[Guest]

Tom,

I'm not quite sure what this code is supposed to do. I'm not much of a
programmer.

The code runs, and replaces numbers with the number 1, but it doesn't seem
to have a pattern.

ANy help would be appreciated.

 

[Tom Ogilvy]

I interpret entire spreadsheet to mean multiple worksheets in a workbook. I
have added code to restrict changes so they won't occur on the sheet that
contain the two columns of data.

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
If sh.Name <> sh1.Name Then
sh1.Cells.Replace What:=cell.Offset(0, 1).Value, _
Replacement:=cell.Value, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
Next sh1
Next cell
End Sub

worked for me as you describe.

The previous code worked as well, but changed the original list.

If it is just one sheet, and changes are in columns C to IV

Sub ABC()
Dim sh 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
sh.Range("C:IV").Replace What:=cell.Offset(0, 1).Value, _
Replacement:=cell.Value, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

Both macros assume the list is in columns A and B of the active sheet with
the first paring starting in row 2.