0

I want to check a cell for specific text in a formula.

If it contains that text I want to select the next cell.

If it doesn't contain that text I want to perform some formatting.

I have tried

If StrComp("A1", "D:\I Stuff\Ser*", vbTextCompare) > 0 Then
'this is checking that "D:\I Stuff\Ser" does exist in A1...
ActiveCell.Offset(0, 0).Range("A1:H1").Select

It doesn't seem to select/find the string in the formula.

Community
  • 1
  • 1
AJWD
  • 3
  • 1
  • 3

1 Answers1

0

The InStr function should be used to check for the string within the Range.Formula property.

dim rng as range
set rng = activecell
if cbool(instr(1, rng.formula, "D:\I Stuff\Ser", vbTextCompare)) Then
    rng.resize(1, 8).Select
else
    'not found. do something else
end if

You may also be interested in How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1
  • Thanks for the reading references, that helps immensely. So using the above code I get a compile error: Invalid or unqualified reference. If I remove the "." from ".Range" the macro runs. However, It is referencing cell A1 when I need the ActiveCell to be the starting point. How do I set the active cell as the beginning of the Range? – AJWD Aug 04 '15 at 20:52
  • I've adjusted the code to reference the [ActiveCell property](https://msdn.microsoft.com/en-us/library/office/aa174738%28v=office.11%29.aspx) as the starting point. Sorry about the extra `.` in `.range`. My bad. I'm just so used to not using activate that I wrote it as if it was within a [With ... End With statement](https://msdn.microsoft.com/en-us/library/wc500chb.aspx). –  Aug 04 '15 at 20:58