InStr Help - Multiple Criteria

  • Thread starter Thread starter TysonE
  • Start date Start date
T

TysonE

This is the line of code I'm working on (Full code below):

myInStr = InStr(c.Formula, "Cog1")

What I would like it to be able to do is find anythinf with a "Cog1"
or "Cog2" or "Cog3." Right now it works perfect with one criteria,
but I would like to open it up to more.

Is there a quick and easy way to do this?

Thanks,

Tyson


Sub Values()
Dim c
Dim myInStr As String
Dim cRange As Range

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

On Error Resume Next
Set cRange = Cells.SpecialCells(xlCellTypeFormulas, 23)

For Each c In cRange
myInStr = InStr(c.Formula, "Cog1")
If myInStr <> 0 Then
Range(c.Address) = Range(c.Address).Value
End If
Next c

On Error GoTo 0
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Will this work?
myInStr = InStr(c.Formula, "Cog1" Or "Cog2" Or "Cog3")

HTH,
Ryan---
 
No, I tired that already. That will actually turn all formulas into
values. Any other ideas?
 
What about just removing the number and then testing for the range
afterwards? Something like this maybe...

For Each c In cRange
myInStr = InStr(c.Formula, "Cog")
If myInStr <> 0 Then
If Mid(c.Formula, myInStr + 3, 1) < 3 and Mid(c.Formula, _
myInStr + 4, 1) Like "[!0-9]" Then
Range(c.Address) = Range(c.Address).Value
End If
End If
Next c
 
Back
Top