Log In
Register
Home
February 27, 2020
Offline
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
See Answer
November 8, 2013
Offline
Should be:
If InStr(1, "FRZ, FREEZER, FREZ, FRE, FREEZ, FR., FREEZETR, FZR, FRS, FEZ, FSD, FS ", cell.Value) > 0 Then
February 27, 2020
Offline
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
November 8, 2013
Offline
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)
