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

Dim rng as Range, sh2 as worksheet, cell as Range
with workbooks("booka.xls").Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End with
set sh2 = Workbooks("Bookb.xls").Worksheets("sheet1")
for each cell in rng
sh.Cells.Replace What:=cell.Value, _
Replacement:=cell.offset(0,1).Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next

If you want to count replacements, you will have to slowly loop through all
the cells in workbook B, writing the code to check it for values and make
the replacement if appropriate (recording the count).

--
Regards,
Tom Ogilvy

"sirdev" wrote in message
...
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

You could run a macro like:

Sub FindandReplace()
Set wbrng = _
Workbooks("workbookB.xls").Sheets("Sheet1").Cells
For i = 1 To 3
wbrng.Replace What:=Sheets("Sheet1").Cells(i, 1), _
Replacement:=Sheets("Sheet1").Cells(i, 2)
Next i
End Sub

---
To run, press Alt+F11, go to Insert Module, paste the
code in, and run it or click back to Excel and run from
the Tools menu.

HTH
Jason
Atlanta, GA

-----Original Message-----
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


.

Thanks for the fast response. I believe the code fragment you provided does
a simple cell be cell replacement where as I need to find/search for a
string from workbookA.sheet1 in workbookB.sheet1. I will look at code more
closely to see if I can alter it a bit. Thanks again for the response.

Also, in general to everyone what a good resources on the net for excel
programming. Yes I know run a check on google. Just off hand does anyone
have any really good ones.

Thanks,

Sriram



"Jason Morin" wrote in message
...
You could run a macro like:

Sub FindandReplace()
Set wbrng = _
Workbooks("workbookB.xls").Sheets("Sheet1").Cells
For i = 1 To 3
wbrng.Replace What:=Sheets("Sheet1").Cells(i, 1), _
Replacement:=Sheets("Sheet1").Cells(i, 2)
Next i
End Sub

---
To run, press Alt+F11, go to Insert Module, paste the
code in, and run it or click back to Excel and run from
the Tools menu.

HTH
Jason
Atlanta, GA

-----Original Message-----
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


.