Find row in cell using VBA

Question

I made myself crazy with this throughout the day, searched high and low, and probably trying to be too sweet, so I was completely stuck.

I try to run simple if then

If the cell contains "%", I would like it to do one thing, and if not another. For reasons that I do not understand, I cannot get it to work. I clearly took a couple of ideas from other sources, 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 built into most using batches or relative ActiveCells. I never know where in column A I'm going to run “% Change,” so Range should always be variable. I want VBA / VBE to do something different when it comes to a cell with "%" in it. SO

Here's 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 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, thanks!

Answer 1

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

Try the following:

 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 a string. I modified your if test to check for no matches first.

Message boxes and .Selects are just for you to see what happens when you go through the code. Take them out as soon as you earn it.

Answer 2

you never change the rng value, so it always points to the starting cell

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

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

Answer 3

You must use the Find , AutoFilter or alternative array methods for the search procedure. Nominee Cycle Ranges Are Too Slow, Worse If They Use Select

In the code below, it will look for the strText variable in the range selected by the user, then it adds any matches to the rng2 range rng2 , which you can then 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