title: Find a string within a cell using VBA
body: <p>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.</p>
  
  <p>I am trying to run a simple if then</p>
  
  <p>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.</p>
  
  <p>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 </p>
  
  <p>Here is what the raw data looks like </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <pre><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 &lt;&gt; 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
  </code></pre>
  
  <p>All help is appreciated, thank you!</p>