Macro Or Formula Please

  steviegee 09:07 07 Sep 2009

Is there a Macro or Formula that will look at a any number in column A and will replace it with another number in column B anywhere on another spreadsheet e.g on sheet 1 in A1 is ABC123 and in B1 is DEF123 so I want it to look for ABC123 anywhere on sheet 2 and replace it with DEF123 and so on down the rows. Thanks, hope its understandable!!

  VoG II 09:56 07 Sep 2009

Sub test()
Dim LR As Long, i As Long
Dim F As Range, c As Range
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Sheets("Sheet2").Cells.Replace What:=.Range("A" & i).Value, Replacement:=.Range("B" & i).Value, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Next i
End With
End Sub

  steviegee 11:44 07 Sep 2009

Thanks worked a treat.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?