Replaced text count

  • Thread starter Thread starter Bartosz
  • Start date Start date
B

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†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
Click to expand...

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
 
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
 
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.
 
"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.
Click to expand...
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
 
Back
Top