[Closed] Excel help (muiltiple find & replace)

how many files do you need to do this to?

Set up a mapping doc with old and new in two columns.

Then use this as a source for vlookups in other files.

If you group worksheets find and replace works on all sheets so only one of each find and replace is needed.

someone much better at VBA than me should be able to show you some code to run a routine using cells.replace on your workbooks and populated from an external source sheet

I think the Southern Yetis idea would work. VBA could do it. I dont think I understand what your asking enough to actually write something for you though.

rich - two columns. First with a list of worksheet names in, second with a list of replacement worksheet names.

I imagine you could write a routine that for the list in A sequentially cells.replace every occurrence of the value in a workbook with the corresponding entry in col B in a given workbook.

let me get this right, you have a customer list and you are changing the names of each customer.

may not be customers but that is how i see it.

best approach i can think of

use a for next loop, size is determined by number of customers on new list.

create a variable this is old name, create a second varible2 this is the first variable offset one cell to pick up the new name.

start at row 1 on the book you want to change

if worksheets.cell()=variable then worksheets.cell()=variable 2
move to next row.

this would be repeated through the whole set of records.
You then go back to the customer match up table and pick the second name. repeat....

If you want me to have a look at the coding and actually get the syntax correct i can have a look a bit later.

silentsparky - my method will definitely work.
If you have hundreds of sheets then I'd consider VBA other wise...

Create your mapping doc.
Write your vlookup.
Paste into sheet.
Copy down.
Copy new codes over old.

I assume all your reporting files are in a consistant format. Should be easy.

try this

Start a new file, in column A, list the names of the files you want to act on, including the .xls extension

In column b, list the current name, with the corresponding replacement in column c.

Run this macro

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/01/2011 by User
'

'
Dim pip(100) As String
Dim pop(100) As String
Dim pap(100) As String
Dim counter As Integer
Dim filecounter As Integer

For filecounter = 1 To 2
pip(filecounter) = Cells(filecounter, 1).Value
For counter = 1 To 3

pop(counter) = Cells(counter, 2).Value
pap(counter) = Cells(counter, 3).Value

Next counter

Workbooks.Open Filename:=pip(filecounter)

Cells.Select

For counter = 1 To 3
Selection.Replace What:=pop(counter), Replacement:=pap(counter), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next counter
ActiveWorkbook.Save
ActiveWorkbook.Close

Next filecounter
End Sub

this assumes all files are in the same folder, including the fiel with the references and macro. It also assume you are only using one sheet and that the files open on that sheet

actually just a quick thought, unless you have lots of worksheets do this, create new workbook with your comparisons. go to data workbook, insert column after name your changing. in this add vlookup() drag this to bottom of column. now copy, paste as value on top of the original name then delete the column you created and filled with vlookups.

Right to answer a few questions;

Need to do this to various files/packs probably 15 in total. Each pack has muiltiple tabs (upto 20) and mpst tabs and a few hundred lines long.

The Southern Yeti - that would work for my reports as I tend to link my formula retrieve to a particular column (although still not too quick to update because of the volume). But I'm sure I'll be asked to update other peoples pack/reports which maynot be in an easy to update format ie. having the account code that needs changing enbedded within a formula in a random column.

what are the names of the tabs?

an example ?

does each file have the same names on the tabs?

Example of tab names:
"UK", "Enterprise", "Consumer", "Voice", Data"

Not each file has the same tabs, but I guess I could tweak the code for each workbook?

You could tweak the code, or if you have a list of the tabs for each workbook, we can include it in the macro