Count Replacements in VBA

noj · Dec 20th 2004

Hi All,

I have the following code.

Code

Cells.Replace What:=ActiveSheet.Name & "!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

If I do a standard Find & Replace then excel will display a msgbox displaying the number of replacements made. I'm struggling to figure out the VBA needed to count the number of replacements so that I can put a msgbox together to alert the user of the number of replacements made.

Appreciate any help given on this topic.

Kindest regards,
Jon

**royUK** · Dec 20th 2004

Re: Count Replacements in VBA

This code will find A and replace with B & give a count

Code

Option Explicit
Sub fndReplace()
Dim i As Long
Dim rng As Range
Dim c As Range
Set rng = ActiveSheet.UsedRange 'saves searching whole sheet
i = 0
For Each c In rng
c.Replace What:=ActiveSheet.Name & "A", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
i = i + 1 'counts changes
Next c
MsgBox i
End Sub

Display More

noj · Dec 20th 2004

Re: Count Replacements in VBA

Thanks very much Roy. Not sure what i is doing though, sorry, I'm still trying to learn this.

I my spreadsheet this is changing 1 cell yet the count is 121? Could you help me understand this please?

Many thanks,
Jon

**royUK** · Dec 20th 2004

Re: Count Replacements in VBA

I think your reference to ActiveSheet.Name could be causing the problem. When I remove it i simply counts each cell that is changed

noj · Dec 20th 2004

Re: Count Replacements in VBA

Thanks again Roy.

I'm trying to build a macro that will remove the active worksheet name from all exisitng formula in the active worksheet. This is because though I prefer to use short names for my worksheets, someone senior to me has insisted that we use more "meaningful" names and these are often very long. Therefore, if we reference several cells from multiple worksheets in a formula and then reference the active worksheet in that same forumla, the characters occassionally exceed the number allowed for in Excel because it inserts the active worksheet name unneccessarily. I'm attempting to build a turnkey operation to shorten this.

Is there any other means of overcoming this problem that you can think of?

Gratefully,
Jon

**royUK** · Dec 20th 2004

Re: Count Replacements in VBA

Hi Jon

I don't think this code can work because it wont find what it is looking for.

noj · Dec 20th 2004

Re: Count Replacements in VBA

Ok, it's not crucial so I'll make do without. Many thanks for your help.

Hoopla3000 · Aug 28th 2019

Quote from royUK

Re: Count Replacements in VBA

This code will find A and replace with B & give a count

Code

Option Explicit

Sub fndReplace()

Dim i As Long

Dim rng As Range

Dim c As Range

Set rng = ActiveSheet.UsedRange 'saves searching whole sheet

i = 0

For Each c In rng

c.Replace What:=ActiveSheet.Name & "A", Replacement:="B", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

i = i + 1 'counts changes

Next c

MsgBox i

End Sub

Display More

This is incorrect. That example actually only counts the number of cells in the used range. It does not count what cells had something found and replaced.

You need something like this: https://stackoverflow.com/a/33627977/1898524

jolivanes · Sep 4th 2019

You could try

Code

Sub Maybe()
Dim c As Range, i As Long
i = 0
For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If InStr(c.Formula, ActiveSheet.Name) > 0 Then
c.Formula = Replace(c.Formula, ActiveSheet.Name & "!", ""): i = i + 1
End If
Next c
MsgBox i
End Sub

MandyRead · Feb 19th 2020

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

Share