Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Search for a test string and if found insert 'x' in clumn 'A'
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 |
#2
|
|||
|
|||
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 -- Gary''s Student "TCL" wrote: 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 |
#3
|
|||
|
|||
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 "TCL" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a Seperator into a number string. | Excel Discussion (Misc queries) | |||
Search string with multiple criteria | Excel Worksheet Functions | |||
Insert characters in a text string | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Q: search in string | Excel Discussion (Misc queries) |