Alex,

Thanks for the help. This code snip looks good. I should be able to modify
this accordingly and add the other functions I have done already into it.

Thanks again.

Sriram





"Alex Guardiet" wrote in message
...
Hi,

I have a more complete version which I think is what you are looking for.
I have provided constants (the first lines starting with "Const") where you
can specify the names of your two worksheets (remember to include the ".xls"
extension) and the cell where you want to see the number of replacements
done. If your two worksheets are already called "WorksheetA" and "Worksheet
B" then it should run as is. Also have both worksheets open before running.
You can run the code from any worksheet:

Option Explicit

Const w1 = "WorkbookA.xls"
Const w2 = "WorkbookB.xls"
Const resultsCell = "c1"

Sub searchAndReplace()

Dim currentPositionW1 As Integer
Dim currentPositionW2 As Integer
Dim searchstring As String
Dim counter As Integer
Dim currentCellVal As String
Dim i As Integer


currentPositionW1 = 0


For i = 0 To 1 Step 0

searchstring =
Workbooks(w1).Sheets(1).Range("a1").Offset(current PositionW1, 0).Value
If searchstring = "" Then Exit For
currentPositionW2 = 0
currentCellVal =
Workbooks(w2).Sheets(1).Range("a1").Offset(current PositionW2, 0).Value

Do While currentCellVal < ""

If currentCellVal = searchstring Then

Workbooks(w2).Sheets(1).Range("a1").Offset(current PositionW2, 0).Value = _

Workbooks(w1).Sheets(1).Range("a1").Offset(current PositionW1, 1).Value
counter = counter + 1
End If
currentPositionW2 = currentPositionW2 + 1
currentCellVal =
Workbooks(w2).Sheets(1).Range("a1").Offset(current PositionW2, 0).Value
Loop
currentPositionW1 = currentPositionW1 + 1
Next

Workbooks(w1).Sheets(1).Range(resultsCell).Value = counter


End Sub

Regards,

Alex

"sirdev" wrote:

Hello everyone,

I am entirely new to macro programming in excel (excel 2000) and I need
to perform a very particular find and replace.

I have two workbooks (one sheet in each) we'll call them workbookA and
workbookB. The first workbook (workbookA) contains two columns the
first cell (A1) contains the search string I need for workbookB and the
cell beside A1 (B1) contains the replace string for workbookB.

Example:

workbookA_sheet1:

column_A column_B
find_string1 replace_string1
find_string2 replace_string2
find_string3 replace_string3


workbookB_sheet1:

any_column
find_string2
find_string3
find_string1

So for instance on the first pass it should search workbookB_sheet1 for
the string "find_string1" (from workbookA_sheet1) if found replace with
"replace_string1" (from workbookA_sheet1). Then use "find_string2" and
do the same thing until the end of workbookA_sheet1 has been reached.

Also, if possible I would like a count of the total number of
fields/cells replaced in workbookB_sheet1 (probably using a incremented
counter on the if statement for the search routine).

I hope all of this is clear. Any help is greatly appreciated. Thanks
in advance for everyones help.


Thanks,
Sriram