I am trying to write a code that will allow me to search for multiple phrases like "energy" and "fuel" within multiple cells in a row like C1:F1 and output a 1 to a specific cell like J1 if any of the phrases are found in any cells. These cells may contain multiple words like "directed energy" and I would still like it to output a 1 if it finds the phrase energy anywhere in the cell. I would also like it to be non case-sensitive if possible!! Thank you so much for the help I really truly appreciate it!
Excel VBA code to search a row of cells for multiple phrases and output "1" if any of them are found
Asked
Active
Viewed 884 times
0
-
Have you written anything so far? And where are the phrases, in a cell range somewhere in your spreadsheet? – Sam Nov 25 '13 at 17:37
-
I've only written a formula that works on one cell, and yes the phrases are in cells on a spreadsheet between C1 and F1. – user3033334 Nov 25 '13 at 18:03
-
This is the formula I have so far =if((iserr(search("energy", c1,1))),0,1) – user3033334 Nov 25 '13 at 18:05
-
And you want this in VBA and not formulas? – guitarthrower Nov 25 '13 at 19:16
-
Also, the SEARCH function in Excel will work regardless of capitalization. – guitarthrower Nov 25 '13 at 19:17
-
Either VBA or a formula, everything I've tried so far hasn't worked – user3033334 Nov 25 '13 at 19:41
-
The function I've written only works on one cell and I want to make it work on a range of cells, specifically one row – user3033334 Nov 25 '13 at 19:43
1 Answers
0
See if this works for you. I want to give @TedWilliams credit for the double "transpose" line, the idea was taken from his excellent post - HERE.
Sub checkString()
Dim s As String, r As Range, vArr, v
Dim b As Boolean
Dim w As Worksheet
vArr = Array("energy", "fuel") 'items to search for, change to suit
Set w = ActiveSheet
Set r = w.Range("C1:F1").Rows(1)
With Application
s = Join(.Transpose(.Transpose(r.Value)))
End With
For Each v In vArr
b = (InStr(1, s, CStr(v), vbTextCompare) > 0)
If b Then Exit For
Next v
w.Range("J1").Value = -b
End Sub
