Find a string within a cell using VBA
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.
Tommy Z
Financial professional using Excel VBA 2007 for various internal uses.
Updated on June 06, 2020Comments
-
Tommy Z about 4 years
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!