Need Help with interesting Find & Replace macro

[sirdev]

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

 

[Tom Ogilvy]

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).

 

[Jason Morin]

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

 

[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

 

[Sriram]

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

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(currentPositionW1, 0).Value

If searchstring = "" Then Exit For
currentPositionW2 = 0
currentCellVal =

Workbooks(w2).Sheets(1).Range("a1").Offset(currentPositionW2, 0).Value

Do While currentCellVal <> ""

If currentCellVal = searchstring Then

Workbooks(w2).Sheets(1).Range("a1").Offset(currentPositionW2, 0).Value = _Workbooks(w1).Sheets(1).Range("a1").Offset(currentPositionW1, 1).Value

counter = counter + 1
End If
currentPositionW2 = currentPositionW2 + 1
currentCellVal =

Workbooks(w2).Sheets(1).Range("a1").Offset(currentPositionW2, 0).Value