Find a string within a cell using VBA

Question

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

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.

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!

Answer 1

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

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

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

Answer 2

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

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.

Answer 3

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

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

also, your InStr test will always fail
True is -1, but the return from InStr will be greater than 0 when the string is found. change the test to 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