Advertisement
ferdinand

Search a value in column &copy row...[Excel VBA]

Feb 25th, 2012
358
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.41 KB | None | 0 0
  1. http://www.techonthenet.com/excel/macros/search_for_string.php
  2. Search for a value in a column and copy row to new sheet for all matching values in Excel 2003/XP/2000/97
  3.  
  4. Sub SearchForString()
  5.  
  6. Dim LSearchRow As Integer
  7. Dim LCopyToRow As Integer
  8.  
  9. On Error GoTo Err_Execute
  10.  
  11. 'Start search in row 4
  12. LSearchRow = 4
  13.  
  14. 'Start copying data to row 2 in Sheet2 (row counter variable)
  15. LCopyToRow = 2
  16.  
  17. While Len(Range("A" & CStr(LSearchRow)).Value) > 0
  18.  
  19. 'If value in column E = "Mail Box", copy entire row to Sheet2
  20. If Range("E" & CStr(LSearchRow)).Value = "Mail Box" Then
  21.  
  22. 'Select row in Sheet1 to copy
  23. Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
  24. Selection.Copy
  25.  
  26. 'Paste row into Sheet2 in next row
  27. Sheets("Sheet2").Select
  28. Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
  29. ActiveSheet.Paste
  30.  
  31. 'Move counter to next row
  32. LCopyToRow = LCopyToRow + 1
  33.  
  34. 'Go back to Sheet1 to continue searching
  35. Sheets("Sheet1").Select
  36.  
  37. End If
  38.  
  39. LSearchRow = LSearchRow + 1
  40.  
  41. Wend
  42.  
  43. 'Position on cell A3
  44. Application.CutCopyMode = False
  45. Range("A3").Select
  46.  
  47. MsgBox "All matching data has been copied."
  48.  
  49. Exit Sub
  50.  
  51. Err_Execute:
  52. MsgBox "An error occurred."
  53.  
  54. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement