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  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   All help is appreciated  thank you

User · Accepted Answer

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.

User · Answer

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  lt  gt  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

User · Answer

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    amp  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  lt  gt  cel1 Address     End If      If Not rng2 Is Nothing Then         For Each cel2 In rng2             Debug Print cel2 Address  amp    contained    amp  strText         Next     Else         MsgBox  No    amp  strText     End If      With Application          ScreenUpdating   True          Calculation   lAppCalc     End With  End Sub

[string] Find a string within a cell using VBA

Examples related to string

Examples related to search

Examples related to excel

Examples related to vba