Microsoft Excel - find text in a worksheet if search then copy the column

Asked By farrukh on 17-Feb-12 12:41 AM
Hi All,

 I need some help by the guru's . i need to search a specific string in the worksheet if find the string then copy the whole column to main sheet at specific column i need it for multiple text in a columns ?

cheers
Somesh Yadav replied to farrukh on 17-Feb-12 01:00 AM
There are two ways:

(1) =NOT(ISERROR(SEARCH(A1,B1))) where cell A1 contains the text you are searching for, e.g. in your case "Eur" and cell B1 contains the text you are searching. I've used this function in Excel 2003 for years and Excel 2010 recently

(2) =ISNUMBER(FIND(A1,B1)) where cell A1 contains the text you are searching for and cell B1 contains the text you are searching. I've tried this function in excel 2010 and it works fine. Havn't tried it in Excel 2003 so cannot comment there

Both functions do what you asked: return "True" when the sought after text is found and "False" when the sought after text is not found. In my opinion the better algorithm is the 2nd because it requires fewer text entries. However cannot comment as to which function computes faster, which is important if you are searching a database with many records. In both cases it is not necessary to bundle this inside an "IF" statement since the functions return "True" or "False" naturally. :painting:
Anoop S replied to farrukh on 17-Feb-12 01:16 AM
Refer this Vba ,  I'm looking for a cell in this column that says "Mail Box". When the string "Mail Box" is found, the corresponding row should be copied to a new sheet.

Sub SearchForString()
 
  Dim LSearchRow As Integer
  Dim LCopyToRow As Integer
 
  On Error GoTo Err_Execute
 
  'Start search in row 4
  LSearchRow = 4
 
  'Start copying data to row 2 in Sheet2 (row counter variable)
  LCopyToRow = 2
 
  While Len(Range("A" & CStr(LSearchRow)).Value) > 0
 
    'If value in column E = "Mail Box", copy entire row to Sheet2
    If Range("E" & CStr(LSearchRow)).Value = "Mail Box" Then
 
      'Select row in Sheet1 to copy
      Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
      Selection.Copy
 
      'Paste row into Sheet2 in next row
      Sheets("Sheet2").Select
      Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
      ActiveSheet.Paste
 
      'Move counter to next row
      LCopyToRow = LCopyToRow + 1
 
      'Go back to Sheet1 to continue searching
      Sheets("Sheet1").Select
 
    End If
 
    LSearchRow = LSearchRow + 1
 
  Wend
 
  'Position on cell A3
  Application.CutCopyMode = False
  Range("A3").Select
 
  MsgBox "All matching data has been copied."
 
  Exit Sub
 
Err_Execute:
  MsgBox "An error occurred."
 
End Sub

Refer:
http://www.techonthenet.com/excel/macros/search_for_string.php
farrukh replied to Anoop S on 23-Feb-12 12:35 PM
Wonderful it works ...


Thank you so much

cheers
Anoop S replied to farrukh on 23-Feb-12 10:19 PM
You are most welcome :)