Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIFS, one criteria range, multiple criteria | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |