使用 VBA 在单元格中查找字符串

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/10904012/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 13:16:08  来源:igfitidea点击:

Find a string within a cell using VBA

stringexcelsearchexcel-vbavba

提问by Tommy Z

I have been driving myself mad with this for a day, searched high and low, and am probably trying to be too cute so am totally stuck.

我已经为此疯狂了一天,四处寻找,可能是因为太可爱了,所以我完全被困住了。

I am trying to run a simple if then

我正在尝试运行一个简单的如果

If a cell contains "%" I'd like it to do one thing, and if not another. For reasons I don't understand I can't get it to work out. I've clearly taken a couple ideas from elsewhere but still can't get it to work.

如果一个单元格包含“%”,我希望它做一件事,如果不是另一件事。由于我不明白的原因,我无法解决它。我显然从其他地方汲取了一些想法,但仍然无法使其发挥作用。

Complicating factors- I don't want to run this on the whole column, just a table, so it is embedded in a larger sub using lots or relative ActiveCells. I never know where in the A column I am going to run into the "% Change" so the Range always has to be variable. I want VBA/VBE to do something different when it comes upon a cell with the "%" in it. SO

复杂的因素 - 我不想在整个列上运行它,只是一个表格,所以它使用批次或相关的 ActiveCell 嵌入到更大的子中。我永远不知道我将在 A 列中的哪个位置遇到“% Change”,因此范围必须始终是可变的。我希望 VBA/VBE 在遇到带有“%”的单元格时做一些不同的事情。所以

Here is what the raw data looks like

这是原始数据的样子

Initial Value (6/30/06)

Value (12/31/06)

Net Additions (9/30/07)

Withdrawal (12/07)

Value (12/31/07)

Withdrawal (2008)

Value (12/31/08)

Addition (8/26/09)

Value (12/31/09)

Value (12/31/10)

Value (12/30/11)

Value (3/31/12)

% Change 1st Quarter

% Change Since Inception

But when I run the following it gets stuck in a bad loop where it should have pulled out into the "If Then" as opposed to the "Else" part of the sub.

但是,当我运行以下命令时,它陷入了一个糟糕的循环中,它应该拉入“If Then”而不是子的“Else”部分。

Sub IfTest()
 'This should split the information in a table up into cells
 Dim Splitter() As String
 Dim LenValue As Integer     'Gives the number of characters in date string
 Dim LeftValue As Integer    'One less than the LenValue to drop the ")"
 Dim rng As Range, cell As Range
 Set rng = ActiveCell

Do While ActiveCell.Value <> Empty
    If InStr(rng, "%") = True Then
        ActiveCell.Offset(0, 0).Select
        Splitter = Split(ActiveCell.Value, "% Change")
        ActiveCell.Offset(0, 10).Select
        ActiveCell.Value = Splitter(1)
        ActiveCell.Offset(0, -1).Select
        ActiveCell.Value = "% Change"
        ActiveCell.Offset(1, -9).Select
    Else
        ActiveCell.Offset(0, 0).Select
        Splitter = Split(ActiveCell.Value, "(")
        ActiveCell.Offset(0, 9).Select
        ActiveCell.Value = Splitter(0)
        ActiveCell.Offset(0, 1).Select
        LenValue = Len(Splitter(1))
        LeftValue = LenValue - 1
        ActiveCell.Value = Left(Splitter(1), LeftValue)
        ActiveCell.Offset(1, -10).Select
    End If
Loop
End Sub

All help is appreciated, thank you!

感谢所有帮助,谢谢!

采纳答案by Jon Crowell

I simplified your code to isolate the test for "%" being in the cell. Once you get that to work, you can add in the rest of your code.

我简化了您的代码以隔离单元格中“%”的测试。一旦你让它工作,你可以添加其余的代码。

Try this:

尝试这个:

Option Explicit


Sub DoIHavePercentSymbol()
   Dim rng As Range

   Set rng = ActiveCell

   Do While rng.Value <> Empty
        If InStr(rng.Value, "%") = 0 Then
            MsgBox "I know nothing about percentages!"
            Set rng = rng.Offset(1)
            rng.Select
        Else
            MsgBox "I contain a % symbol!"
            Set rng = rng.Offset(1)
            rng.Select
        End If
   Loop

End Sub

InStrwill return the number of times your search text appears in the string. I changed your iftest to check for no matches first.

InStr将返回您的搜索文本出现在字符串中的次数。我将您的if测试更改为首先检查没有匹配项。

The message boxes and the .Selectsare there simply for you to see what is happening while you are stepping through the code. Take them out once you get it working.

消息框和.Selects只是为了让您在单步执行代码时查看正在发生的事情。一旦你开始工作,就把它们拿出来。

回答by brettdj

For a search routine you should look to use Find, AutoFilteror variant array approaches. Range loops are nomally too slow, worse again if they use Select

对于搜索例程,您应该考虑使用Find,AutoFilter或变体数组方法。范围循环通常太慢,如果他们使用Select

The code below will look for the strText variable in a user selected range, it then adds any matches to a range variable rng2which you can then further process

下面的代码将在用户选择的范围内查找 strText 变量,然后将任何匹配项添加到范围变量中rng2,然后您可以进一步处理

Option Explicit

Const strText As String = "%"

Sub ColSearch_DelRows()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim cel1 As Range
    Dim cel2 As Range
    Dim strFirstAddress As String
    Dim lAppCalc As Long


    'Get working range from user
    On Error Resume Next
    Set rng1 = Application.InputBox("Please select range to search for " & strText, "User range selection", Selection.Address(0, 0), , , , , 8)
    On Error GoTo 0
    If rng1 Is Nothing Then Exit Sub

    With Application
        lAppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set cel1 = rng1.Find(strText, , xlValues, xlPart, xlByRows, , False)

    'A range variable - rng2 - is used to store the range of cells that contain the string being searched for
    If Not cel1 Is Nothing Then
        Set rng2 = cel1
        strFirstAddress = cel1.Address
        Do
            Set cel1 = rng1.FindNext(cel1)
            Set rng2 = Union(rng2, cel1)
        Loop While strFirstAddress <> cel1.Address
    End If

    If Not rng2 Is Nothing Then
        For Each cel2 In rng2
            Debug.Print cel2.Address & " contained " & strText
        Next
    Else
        MsgBox "No " & strText
    End If

    With Application
        .ScreenUpdating = True
        .Calculation = lAppCalc
    End With

End Sub

回答by SeanC

you never change the value of rngso it always points to the initial cell

你永远不会改变的值rng所以它总是指向初始单元格

copy the Set rng = rng.Offset(1, 0)to a new line before loop

Set rng = rng.Offset(1, 0)在循环之前复制到一个新行

also, your InStrtest will always fail
Trueis -1, but the return from InStrwill be greater than 0 when the string is found. change the test to remove = True

此外,您的InStr测试将始终失败
True为 -1,但InStr找到字符串时,返回值将大于 0。将测试更改为 remove = True

new code:

新代码:

Sub IfTest()
 'This should split the information in a table up into cells
 Dim Splitter() As String
 Dim LenValue As Integer     'Gives the number of characters in date string
 Dim LeftValue As Integer    'One less than the LenValue to drop the ")"
 Dim rng As Range, cell As Range
 Set rng = ActiveCell

Do While ActiveCell.Value <> Empty
    If InStr(rng, "%") Then
        ActiveCell.Offset(0, 0).Select
        Splitter = Split(ActiveCell.Value, "% Change")
        ActiveCell.Offset(0, 10).Select
        ActiveCell.Value = Splitter(1)
        ActiveCell.Offset(0, -1).Select
        ActiveCell.Value = "% Change"
        ActiveCell.Offset(1, -9).Select
    Else
        ActiveCell.Offset(0, 0).Select
        Splitter = Split(ActiveCell.Value, "(")
        ActiveCell.Offset(0, 9).Select
        ActiveCell.Value = Splitter(0)
        ActiveCell.Offset(0, 1).Select
        LenValue = Len(Splitter(1))
        LeftValue = LenValue - 1
        ActiveCell.Value = Left(Splitter(1), LeftValue)
        ActiveCell.Offset(1, -10).Select
    End If
Set rng = rng.Offset(1, 0)
Loop

End Sub