macro to find and replace one cell with another.

[salanssr]

I have a problem and I am a rookie when it comes to excel macros.

We create parts catalogs and have to run a supersession for parts in
our files. This is very redundant and I would like to create a macro
to automatically search for "old part" and replace with "new part".

I am trying to take a string from a given cell in worksheet-1 (range
B1) , search for the same string in worksheet-2, replace string 1 in
worksheet 2 with another part from worksheet 1 (range G1). Then
delete the entire row and go to the next row and repeat the same
operation. I attached the macro I am trying to use with little
success. It only changes 1 cell in file 2 instead of searching for a
new number and the program keeps running until I can break. Hope this
makes sense.

Sub supersessions()
'
' supersessions Macro
' Macro recorded 9/10/2003 by stu
'

'
Dim strOld As String
Dim strNew As String
Do Until strOld = Null
strOld = Range("B1").Value
strNew = Range("G1").Value
Windows("651724M9.xls").Activate
Cells.Find strOld
Selection.Value = strNew
Windows("SSRPT.xls").Activate
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Loop

End Sub

Hope someone can help, for I am lost.

Stu

 

[Dave Peterson]

I think that this does what you want:

Option Explicit
Sub supersessions()

Dim wordWks As Worksheet
Dim changeWks As Worksheet
Dim lastRow As Long
Dim iRow As Long
Dim strOld As String
Dim strNew As String

Set wordWks = Workbooks("book3.xls").Worksheets("sheet2")
Set changeWks = Workbooks("book3.xls").Worksheets("sheet1")

With wordWks
If IsEmpty(.Range("b1")) Then
MsgBox "no word in B1!"
Exit Sub
ElseIf IsEmpty(.Range("b2")) Then
lastRow = 1
Else
lastRow = .Range("b1").End(xlDown).Row
End If

For iRow = 1 To lastRow
strOld = .Cells(iRow, "B").Value
strNew = .Cells(iRow, "G").Value

If strOld = "" _
Or strNew = "" Then
'do nothing
Else
With changeWks.Cells
.Replace What:=strOld, Replacement:=strNew, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False
End With
End If
Next iRow

.Rows(1).Resize(lastRow).Delete

End With

End Sub

Instead of using .Find, I just used .Replace (like with Edit|Replace).

wordWks is the worksheet that holds the list of words in B & G.
changeWks is the worksheet that gets updated.

In my example they pointed to the same workbook, but you can change them to
anything you want. (as long as the workbooks are open and the worksheets
exist.)