InStr Help - Multiple Criteria
 

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

InStr Help - Multiple Criteria
 

Will this work?
myInStr = InStr(c.Formula, "Cog1" Or "Cog2" Or "Cog3")

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"TysonE" wrote:

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

InStr Help - Multiple Criteria
 

No, I tired that already. That will actually turn all formulas into
values. Any other ideas?

On Aug 3, 9:56*am, ryguy7272
wrote:
Will this work?
myInStr = InStr(c.Formula, "Cog1" Or "Cog2" Or "Cog3")

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..



"TysonE" wrote:
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- Hide quoted text -

- Show quoted text -

InStr Help - Multiple Criteria
 

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

--
Rick (MVP - Excel)


"TysonE" wrote in message
...
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