前端之家

  1. 首页
  2. 问答

计算工作表中的特定字符

• 问答

我有一个宏来查找公式，并在找到公式后将值粘贴到其中

例如，它找到vlookup，然后更改单元格中的值以粘贴特殊

现在唯一的问题是我无法计算Vlookup出现的次数。目前，我将计数器设置为300，但请让我知道是否可以计算公式。我尝试使用countif，但它查看的值不在公式中

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

如果有人可以帮助我找到验证码，我将不胜感激

谢谢

jscj4567 回答：计算工作表中的特定字符

我这样理解

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
countexcelfindreplacevba
本文链接：https://www.f2er.com/3134942.html

大家都在问