• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

[If InStr....or......or.....or.....or....etc... then] Short write VBA Code request

Hi,

Sorry for Poor English Grammer

I am using Excel 2013 32 bit

if word string (" FRZ" or "FREEZER" or "FREZ" or "FRE" or "FREEZ" or "FR." or "FREEZETR" or "FZR" or "FRS" or "FEZ" or "FSD" or "FS ") available in Range ("S2:S45265") then return the word "Freezer"

But trying code, but not work "If InStr(cell.Value, ["FRZ","FREEZER","FREZ","FRE","FREEZ","FR.","FREEZETR","FZR","FRS","FEZ","FSD","FS "]) > 0 Then"

I used longly write code below in bold, I need any short write code for string word (finding) [If InStr....or......or.....or.....or....etc... then]

Macro Code:


>>> use code - tags <<<
Code:
Sub Search_Range_For_Text()
'loop through a range of cells to test if the cells contain some text:

Dim cell As Range

    For Each cell In Range("S2:S45265") 'Searching Column
        If InStr(cell.Value, "FRZ") > 0 Or InStr(cell.Value, "FREEZER") > 0 Or InStr(cell.Value, "FREZ") > 0 Or InStr(cell.Value, "FRE") > 0 Or InStr(cell.Value, "FREEZ") > 0 Or InStr(cell.Value, "FR.") > 0 Or InStr(cell.Value, "FREEZETR") > 0 Or InStr(cell.Value, "FZR") > 0 Or InStr(cell.Value, "FRS") > 0 Or InStr(cell.Value, "FEZ") > 0 Or InStr(cell.Value, "FSD") > 0 Or InStr(cell.Value, "FS ") > 0 Then
            cell.Offset(0, 8).Value = "Freezer"  'Result Column
        End If
    Next cell

End Sub

Kindly anybody helps me for short write VBA code...
Thanks for advance
 
Last edited by a moderator:
sivaprakasam
Before You'll try to run below, You should make needed modifications as written below.
Code:
Sub Do_It_With_AutoFilter()
    With ActiveSheet
'    here Your whole range = all columns eg A2:ZZ45265
        .Range("A2:ZZ45265").AutoFilter Field:=1, Criteria1:=Array("FRZ", "FREEZER", "FREZ", "FRE", "FREEZ", "FR.", "FREEZETR", "FZR", "FRS", "FEZ", "FSD", "FS"), Operator:=xlFilterValues
'    here Your 'Freezer'-results column eg ZZ
        .Range("ZZ2:ZZ45265") = "Freezer"
'   here same range as in 1st range
        .Range("A2:ZZ45265").AutoFilter
    End With
End Sub
 
Last edited:
try:
Code:
Sub Search_Range_For_Text2()
Dim cell As Range

yy = Array("FRZ", "FRE", "FR.", "FZR", "FRS", "FEZ", "FSD", "FS ")
For Each cell In Range("S2:S45265")    'Searching Column
  If Not IsError(Application.Match(9E+99, Application.Find(yy, cell.Value))) Then cell.Offset(0, 8).Value = "Freezer"
Next cell
End Sub
Don't know if it's efficient or not.
(I missed out "FREEZER", "FREZ", "FREEZ", "FREEZETR" because they all be caught by "FRE")
 
sivaprakasam
Before You'll try to run below, You should make needed modifications as written below.
Code:
Sub Do_It_With_AutoFilter()
    With ActiveSheet
'    here Your whole range = all columns eg A2:ZZ45265
        .Range("A2:ZZ45265").AutoFilter Field:=1, Criteria1:=Array("FRZ", "FREEZER", "FREZ", "FRE", "FREEZ", "FR.", "FREEZETR", "FZR", "FRS", "FEZ", "FSD", "FS"), Operator:=xlFilterValues
'    here Your 'Freezer'-results column eg ZZ
        .Range("ZZ2:ZZ45265") = "Freezer"
'   here same range as in 1st range
        .Range("A2:ZZ45265").AutoFilter
    End With
End Sub

thq for help, this Results is come to all cells fill "Freezer", filter condition not match
 
Last edited:
try:
Code:
Sub Search_Range_For_Text2()
Dim cell As Range

yy = Array("FRZ", "FRE", "FR.", "FZR", "FRS", "FEZ", "FSD", "FS ")
For Each cell In Range("S2:S45265")    'Searching Column
  If Not IsError(Application.Match(9E+99, Application.Find(yy, cell.Value))) Then cell.Offset(0, 8).Value = "Freezer"
Next cell
End Sub
Don't know if it's efficient or not.
(I missed out "FREEZER", "FREZ", "FREEZ", "FREEZETR" because they all be caught by "FRE")


Hi got error in "yy=Array("

i have set the code is "Dim yy() As Variant"

then this work your code

Thanks U for help
:DD:DD:DD
 
sivaprakasam
As I wrote with red text
Before You'll try to run below, You should make needed modifications as written below.
After that it will work, it'll filter Your keywords and changes all needed cells in one time.
Of course, it's Your choice.
 
Back
Top