

February 27, 2020

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:
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

Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service

November 8, 2013



February 27, 2020

Hi, Tnq for help,
I try above your code, but the result has come the blank,
what I am doing wrongly, I don't know
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("A2:A10022") 'Searching Column
If InStr(1, "FRZ, FREEZER, FREZ, FRE, FREEZ, FR., FREEZETR, FZR, FRS, FEZ, FSD, FS ", cell.Value) > 0 Then
cell.Offset(0, 2).Value = "Freezer" 'Result Column
End If
Next cell
End Sub

Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service

November 8, 2013

Try this 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("A2:A10022") 'Searching Column
If FindMatch(cell.Value) = True Then
cell.Offset(0, 1).Value = "Freezer" 'Result Column
End If
Next cell
End Sub
Function FindMatch(ByVal Text As String) As Boolean
Dim re As Object: Set re = CreateObject("vbscript.regexp")
re.Pattern = "FRZ|FREEZER|FREZ|FRE|FREEZ|FR\.|FREEZETR|FZR|FRS|FEZ|FSD|FS "
If re.Execute(Text).Count > 0 Then FindMatch = True
End Function

Answers Post
1 Guest(s)
