Search for a test string and if found insert 'x' in clumn 'A'

T

TCL

Anyone have a script to
1. search an entire worksheet (say A1 through AZ2500) for a specific text
string (like 'purchase') and
2. if the string is found anywhere in the row insert an 'x' in column 'A'
(of the IDed row).


Thanks
 
G

Gary''s Student

First select a pile of cells and then run:

Sub findit()
Dim s As String
Dim r As Range
s = "purchase"
For Each r In Selection
If InStr(1, r, s) Then
Cells(r.Row, 1) = "x"
End If
Next
End Sub
 
B

B. R.Ramachandran

Hi,

I have two questions.
(1) Would the string you would search for (e.g., 'purchase') occur
independently in one more more cells, or be a part of longer strings(e.g.,
'total purchase' or 'total purchase in 2005)?
(2) What do you mean by, "if the string is found anywhere in the row insert
an 'x' in column 'A' (of the IDed row)"? You mean, the contents of all the
cells in that row are shifted one column to the right to INSERT a new column
A (i.e., An, ... AZn are shifted to Bn, ...BAn, where 'n' is a row number)?

If the search string would occur independently (and not as part of a longer
string), and if my assumption 2 is correct, then

You would be better of to insert a new column (Column A) first, and then use
the following formula in A1 (and then fill-down the formula to A2500).

=IF(COUNTIF($B1:$BA1,"purchase")>0,"x","")

Regards,
B. R. Ramachandran
 
Top