CODEWITHSUNDEEP
excelvbareplacecountfind
Ankit Kulshrestha
Ankit Kulshrestha

Reputation: 27

Count a specific character in a sheet

I have a made a macro to find a formula and paste there values once found.

For example it finds the vlookup and then changes the values in the cell to paste special.

I want to count the number of times Vlookup is appearing. I set the counter at 300 but how can I count the formula? I tried using countif but it looks at the value not in the formula.

Sub Test_M()

' Find a particular text or Formula and replace it with values

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim Ra As Range
Dim counter As Integer

fnd = "=VLOOKUP("
rplc = "Test"

Sheets(1).Activate

' Goes to first sheet otherwise it was not replacing the values

For Each sht In ActiveWorkbook.Worksheets
    For counter = 1 To 300
    ' I want counter to be dynamic based on the numbers of Vlookup formulas        
        Set Ra = sht.Cells.Find(What:=fnd, LookIn:=xlFormulas, LookAt:=xlPart, _
          SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False)

        If Ra Is Nothing Then
            MsgBox ("Not found Moving to next sheet")

            On Error GoTo QUITIT
            'For the Last sheet when there is no sheets left

            Worksheets(ActiveSheet.Index + 1).Select
            counter = 300

        Else
            Range(Ra.Address).Value = Range(Ra.Address)
            ' Changes Formula to Values
        End If
    Next

Next sht

QUITIT:

MsgBox "Completed"
End Sub

Upvotes: 0

Views: 52

Answers (1)

Storax
Storax

Reputation: 12167

I understood it like that

Option Explicit

Sub Test_M()

    ' Find a particular text or Formula and replace it with values

    Dim sht As Worksheet
    Dim fnd As Variant
    Dim Ra As Range
    Dim counter As Integer

    fnd = "=VLOOKUP("

    For Each sht In ActiveWorkbook.Worksheets

        With sht.Cells
            Set Ra = .Find(What:=fnd, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)

            Dim prevAddress As String
            If Not Ra Is Nothing Then
                prevAddress = Ra.Address
                Do
                    Ra.Value = Ra.Value
                    counter = counter + 1  ' Keeping track how often VLOOKUP was replaced
                    Set Ra = .FindNext(Ra)
                    If Ra Is Nothing Then
                        Exit Do
                    End If
                Loop While Ra.Address <> prevAddress
            End If
        End With
        'MsgBox ("Not found Moving to next sheet")

    Next sht

    MsgBox "Completed - Counter is " & counter

End Sub

Upvotes: 1

Related Questions