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
Should be:
If InStr(1, "FRZ, FREEZER, FREZ, FRE, FREEZ, FR., FREEZETR, FZR, FRS, FEZ, FSD, FS ", cell.Value) > 0 Then
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
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
