使用VBA查找单元格中的字符串 [英] Find a string within a cell using VBA

问题描述

我一直在开车自己一天，搜索高低，可能试图太可爱，所以完全卡住了。

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.

复杂因素 - 我不想在整个列上运行，只是一个表，所以它嵌入在一个更大的子使用很多或相对ActiveCells。我从来不知道A栏中的哪一个我将遇到％变化，所以范围总是必须是可变的。我想要VBA / VBE做一些不同的事情，当它在一个单元格与其中的％。 SO

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

这里是原始数据的样子

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

但是当我运行以下它被卡在一个坏的循环，它应该拉出进入If Then而不是Sub的Else部分。

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.

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

所有的帮助不胜感激，谢谢！ / p>

All help is appreciated, thank you!

推荐答案

我简化了代码，以隔离测试％在单元格中。一旦你得到它的工作，你可以添加其余的代码。

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.

尝试这样：

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

InStr 将返回搜索文本出现在字符串中的次数。我更改了，如果测试以检查没有比赛。

InStr will return the number of times your search text appears in the string. I changed your if test to check for no matches first.

消息框和。选择只是为了看你正在逐步完成代码时发生了什么。一旦你得到它，就把它们拿出来。

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

这篇关于使用VBA查找单元格中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！