Replaced text count

[Bartosz]

Hello,

I made macro wich repace texts in other one in many Excel files (using
replace method). I think about making report after to know how many changes
was made in each files:

text to replace new text
aaaa bbbbb
cc XXXXX
......

Files:
1.xls, 2.xls

Repor:
File sheet text to replace new text count
1.xls Sheet1 aaaa bbbbb 3
1.xls Sheet1 cc XXXXX 3
1.xls Sheet2 aaaa bbbbb 0
1.xls Sheet2 cc XXXXX 34
....
2.xls Sheet1 aaaa bbbbb 3
2.xls Sheet1 cc XXXXX 3
....
Count column contan number of changes as it is show after "hand made"
replace (Ctr+H).

Bartosz

 

[Bartosz]

„Bartosz†pisze:

Hello,

I made macro wich repace texts in other one in many Excel files (using
replace method). I think about making report after to know how many changes
was made in each files:

text to replace new text
aaaa bbbbb
cc XXXXX
.....

Files:
1.xls, 2.xls

Repor:
File sheet text to replace new text count
1.xls Sheet1 aaaa bbbbb 3
1.xls Sheet1 cc XXXXX 3
1.xls Sheet2 aaaa bbbbb 0
1.xls Sheet2 cc XXXXX 34
...
2.xls Sheet1 aaaa bbbbb 3
2.xls Sheet1 cc XXXXX 3
...
Count column contan number of changes as it is show after "hand made"
replace (Ctr+H).

Bartosz

I use this code in macro:
For Each ws In Worksheets
Worksheets(ws.Name).Activate
For X = 1 To ilelinii - 1
Cells.Replace What:=strTabela(X, 1),
Replacement:=strTabela(X, 2), LookAt:=dopasowanie, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Next X
Next ws


strTabela(X, 2) is table with texts to change and new one.
dopasowanie is xlWhole/xlPart define by user

Probably to count chages I will need another one.

Bartosz

 

[OssieMac]

No sure how much help you want here but here is an example of establishing
the number of replacements.

It involves counting the number of strings to be replaced before actually
making the replacements. (I don't know of any way of returning the number of
replacements from the Replace code.)

I have assumed that you can make a table of the results returned in the
variable dblNumberReplaces.

With Sheets("Sheet1")

dblNumberReplaces = WorksheetFunction _
.CountIf(.Cells, strToReplace)

.Cells.Replace What:=strToReplace, _
Replacement:=strReplacement, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=True, _
SearchFormat:=False, _
ReplaceFormat:=False
End With

 

[Bartosz]

„OssieMac†pisze:

I have assumed that you can make a table of the results returned in the
variable dblNumberReplaces.

With Sheets("Sheet1")

dblNumberReplaces = WorksheetFunction _
.CountIf(.Cells, strToReplace)

.Cells.Replace What:=strToReplace, _
Replacement:=strReplacement, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=True, _
SearchFormat:=False, _
ReplaceFormat:=False
End With

It will work fine, but only with parametr LookAt:=xlWhole, but when I use
LookAt:=xlPart count number and replaced number will be differend.
I try with:

dblNumberReplaces = WorksheetFunction.CountIf(.Cells, "*"&strToReplace&"*")

but with two strings in one cell (like Cells(x,y).Value = "strToReplace
123456 strToReplace") this cell is count as one.

Regards,
Bartosz

 

[OssieMac]

I don't often give up but I'm giving up on this one. I thought I might be
able to do what you want but too many other problems kept raising their ugly
head.

 

[Bartosz]

"OssieMac†pisze:

I don't often give up but I'm giving up on this one. I thought I might be
able to do what you want but too many other problems kept raising their ugly
head.

Thanks anyway!
I don't want to change my macro, but I will need to do this.
I will change replace function to find in loop from Ms Help:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Inside this do ... loop I can cound changes in files.

Best Regards,
Bartosz