Hello guys,



I have 14 files (every of them with several tabs) where there are several
replacements to do.

55 old values need to be replaced with 12 new strings, f. ex:



old value_ to be replaced with new value_

apple 1 orange1

apple 2 orange1

apple 32 orange1

apple 8 orange22

pineapple21 orange22

pineapple5 orange22

pineapple3 orange22

pineapple43 orange22

grape1 orange444

grape122 orange444

.. .



Could you help me to write a macro that will :

1) treat all the files/tabs in the specified folder (f. ex. C:\test) -
I'll put there all the files that I want to change

2) make changes to them (all files, all tabs) based on the open file
"replacement_template.xls" (where I have column A (old values) and B (new
values))

3) highlight (in yellow) all the changed cells and put in comments to
the every changed cell the old value



Seems at the same time easy and complex to do.

In advance many thanks for any hints/draft solutions you could provide!

Regards,

Mark

Try this:

Sub AdvancedReplaceMacro()
folderspec = "c:\test"
Dim fs, f, f1, fc
Dim strReplacementText As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc 'runs through all files
Workbooks.Open f1
For intCount = 1 To Workbooks(f1.Name).Worksheets.Count 'runs
through all sheets
Workbooks(f1.Name).Worksheets(intCount).Select
Cells(1, 1).Select 'selects cell A1 - you may need to
change this
Do
Select Case ActiveCell.Value 'changes values
Case "apple 1", "apple 2", "apple 32"
strReplacementText = "orange1"
Case "apple 8", "pineapple21", "pineapple5",
"pineapple3", "pineapple43"
strReplacementText = "orange22"
Case Else
MsgBox "Cannot find '" & ActiveCell.Value & "'.",
vbInformation
strReplacementText = ""
End Select
If strReplacementText < "" Then Call
UpdateValue(strReplacementText)

strReplacementText = ""
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row =
Cells.SpecialCells(xlCellTypeLastCell).Row 'loop until end
Next intCount
Next
End Sub
Function UpdateValue(strReplacementText As String)
'updates colour and comment
Selection.Interior.ColorIndex = 36
Range(ActiveCell.Address).AddComment
Range(ActiveCell.Address).Comment.Visible = False
Range(ActiveCell.Address).Comment.Text Text:="Old Value:" & Chr(10)
& ActiveCell.Value
Range(ActiveCell.Address).Value = strReplacementText
End Function


markx wrote:
Hello guys,



I have 14 files (every of them with several tabs) where there are several
replacements to do.

55 old values need to be replaced with 12 new strings, f. ex:



old value_ to be replaced with new value_

apple 1 orange1

apple 2 orange1

apple 32 orange1

apple 8 orange22

pineapple21 orange22

pineapple5 orange22

pineapple3 orange22

pineapple43 orange22

grape1 orange444

grape122 orange444

. .



Could you help me to write a macro that will :

1) treat all the files/tabs in the specified folder (f. ex. C:\test) -
I'll put there all the files that I want to change

2) make changes to them (all files, all tabs) based on the open file
"replacement_template.xls" (where I have column A (old values) and B (new
values))

3) highlight (in yellow) all the changed cells and put in comments to
the every changed cell the old value



Seems at the same time easy and complex to do.

In advance many thanks for any hints/draft solutions you could provide!

Regards,

Mark

Thanks a lot, FunkySquid!
It's excellent!!!

i was just wondering if you (or somebody else) know how to modify this code
in two points:
1) Close every workbook once it's changed
2) the cells to replace can be anywhere on the sheet (and not only in one
specific column, like "A" in your exemple) <I was probably not very clear on
this one in my e-mail, sorry about this

Once again, a big thank to you,
Cheers,
Mark

"FunkySquid" wrote in message
oups.com...
Try this:

Sub AdvancedReplaceMacro()
folderspec = "c:\test"
Dim fs, f, f1, fc
Dim strReplacementText As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc 'runs through all files
Workbooks.Open f1
For intCount = 1 To Workbooks(f1.Name).Worksheets.Count 'runs
through all sheets
Workbooks(f1.Name).Worksheets(intCount).Select
Cells(1, 1).Select 'selects cell A1 - you may need to
change this
Do
Select Case ActiveCell.Value 'changes values
Case "apple 1", "apple 2", "apple 32"
strReplacementText = "orange1"
Case "apple 8", "pineapple21", "pineapple5",
"pineapple3", "pineapple43"
strReplacementText = "orange22"
Case Else
MsgBox "Cannot find '" & ActiveCell.Value & "'.",
vbInformation
strReplacementText = ""
End Select
If strReplacementText < "" Then Call
UpdateValue(strReplacementText)

strReplacementText = ""
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row =
Cells.SpecialCells(xlCellTypeLastCell).Row 'loop until end
Next intCount
Next
End Sub
Function UpdateValue(strReplacementText As String)
'updates colour and comment
Selection.Interior.ColorIndex = 36
Range(ActiveCell.Address).AddComment
Range(ActiveCell.Address).Comment.Visible = False
Range(ActiveCell.Address).Comment.Text Text:="Old Value:" & Chr(10)
& ActiveCell.Value
Range(ActiveCell.Address).Value = strReplacementText
End Function


markx wrote:
Hello guys,



I have 14 files (every of them with several tabs) where there are several
replacements to do.

55 old values need to be replaced with 12 new strings, f. ex:



old value_ to be replaced with new value_

apple 1 orange1

apple 2 orange1

apple 32 orange1

apple 8 orange22

pineapple21 orange22

pineapple5 orange22

pineapple3 orange22

pineapple43 orange22

grape1 orange444

grape122 orange444

. .



Could you help me to write a macro that will :

1) treat all the files/tabs in the specified folder (f. ex.
C:\test) -
I'll put there all the files that I want to change

2) make changes to them (all files, all tabs) based on the open
file
"replacement_template.xls" (where I have column A (old values) and B (new
values))

3) highlight (in yellow) all the changed cells and put in comments
to
the every changed cell the old value



Seems at the same time easy and complex to do.

In advance many thanks for any hints/draft solutions you could provide!

Regards,

Mark

If you have more than a handful of replacement values, you may instead want
to creat a lookup table, then have the macro reference that. That would
eliminate your need for a "case" for every set of values.


"markx" wrote:

Hello guys,



I have 14 files (every of them with several tabs) where there are several
replacements to do.

55 old values need to be replaced with 12 new strings, f. ex:



old value_ to be replaced with new value_

apple 1 orange1

apple 2 orange1

apple 32 orange1

apple 8 orange22

pineapple21 orange22

pineapple5 orange22

pineapple3 orange22

pineapple43 orange22

grape1 orange444

grape122 orange444

.. .



Could you help me to write a macro that will :

1) treat all the files/tabs in the specified folder (f. ex. C:\test) -
I'll put there all the files that I want to change

2) make changes to them (all files, all tabs) based on the open file
"replacement_template.xls" (where I have column A (old values) and B (new
values))

3) highlight (in yellow) all the changed cells and put in comments to
the every changed cell the old value



Seems at the same time easy and complex to do.

In advance many thanks for any hints/draft solutions you could provide!

Regards,

Mark