搜索特定的单词并复制到另一张表 [英] Search a specific word and copy line to another Sheet

问题描述

我有一个电子表格，包括A-P和第1行至2016年的列（并且仍在增长）。我正在寻找一种简单的方法来搜索电子表格中的特定单词，例如Gap，并将包含单词gap的行复制到Sheet2。我想，如果它可以使用一个盒子，我把这个词，所以我可以搜索不同的东西，根据需要。

我不希望电子表格变得更小（这是一个操作项目列表，我需要它进行搜索，直到达到空白）。

我该怎么做？

解决方案

 '所有变量必须声明为
 Option Explicit 
 Sub CopyData（）
 
'该变量持有一个搜索短语，声明为变体，因为它可能是文本或数字
 Dim vSearch As Variant 
'这三个变量被声明为长，从技术上讲，循环可能超过32k（整数），因此使用long 
更安全b Dim i As Long 
 Dim k As Long 
 Dim lRowToCopy As Long 
 
'该宏提示用户输入搜索短语
 vSearch = InputBox（Search）
'varialbe我最初声明为1  - 宏从第一行开始计算
i = 1 
'宏将循环，直到找到没有记录的行
'我称为标准XLS函数COUNTA来计数非空白单元格
'如果宏找到没有记录的行退出循环
 Do Until WorksheetFunction.CountA（Sheets（Main）。Rows（i））= 0 
 
'这里我让宏继续运行，尽管有可能的错误（下面的解释）
 On Error Resume Next 
 lRowToCopy = 0 
'如果Find方法找不到值VBA返回错误，这就是为什么我允许宏运行尽管如此。如果错误变量lRowToCopy保持0值
'如果Find方法找到搜索值，则将行号分配给var lRowToCopy 
 lRowToCopy = Sheets（Main）。Rows（i）.Find（What ：= vSearch，LookIn：= xlValues，LookAt：= xlPart，SearchOrder：= xlByRows）.Row 
'这里我们允许宏显示错误消息
错误GoTo 0 
 
 '如果var lRowToCopy不等于0，则表示已找到具有搜索值的行
如果lRowToCopy> 0然后
 
'这个循环寻找第二张表中的第一个空白行，我也使用COUNTA找到绝对空行
对于k = 1到表格（ToCopy）。计数
 
'当行被发现时，宏执行复制粘贴操作
如果WorksheetFunction.CountA（Sheets（ToCopy）。Rows（k））= 0然后
 
表格（Main）。行（i）.Copy 
表格（ToCopy）。选择
行（k）。选择
 ActiveSheet.Paste 
'不要忘记退出循环，因为它将填充第二张表中的所有空行
退出
 
结束如果
下一个k 
 
结束如果
 
i = i + 1 
循环
 
 End Sub 
  

I have a spreadsheet that consists of columns from A-P and lines 1 to 2016 (and still growing). I am looking for an easy way to search the spreadsheet for a specific word, for example "Gap", and have the lines that contain the word "gap" copied to Sheet2. I would like if it could use a box where I put the word in, so I can search for different things as needed.

I don't expect the spreadsheet to get any smaller (this is an action item list, I need it to search until it reaches a blank line).

How can I do this?

解决方案

'all variables must be declared    
Option Explicit
Sub CopyData()

'this variable holds a search phrase, declared as variant as it might be text or number
Dim vSearch As Variant
'these three variables are declared as long, technically the loop might exceed 32k (integer) therefore it is safer to use long
Dim i As Long
Dim k As Long
Dim lRowToCopy As Long

'the macro prompts a user to enter the search phrase
vSearch = InputBox("Search")
'varialbe i initially declared as 1 - macro starts calculations from the 1st row
i = 1
'macro will loop until it finds a row with no records
'I called a standard XLS function COUNTA to count the number of non-blank cells
'if the macro finds a row with no records it quits the loop
Do Until WorksheetFunction.CountA(Sheets("Main").Rows(i)) = 0

 'here I let the macro to continue its run despite a possible errors (explanation below)
  On Error Resume Next
  lRowToCopy = 0
 'if Find method finds no value VBA returns an error, this is why I allowed macro to run despite that. In case of error variable lRowToCopy keeps 0 value
 'if Find method finds a searched value it assigns the row number to var lRowToCopy
  lRowToCopy = Sheets("Main").Rows(i).Find(What:=vSearch, LookIn:=xlValues,     LookAt:=xlPart, SearchOrder:=xlByRows).Row
 'here we allow macro to disiplay error messages
  On Error GoTo 0

 'if var lRowToCopy does not equal to 0 that means a row with a searched value has been found
  If lRowToCopy > 0 Then

    'this loop looks for the first blank row in 2nd sheet, I also used COUNTA to find absolutely empty row
    For k = 1 To Sheets("ToCopy").Rows.Count

      'when the row is found, the macro performs copy-paste operation
      If WorksheetFunction.CountA(Sheets("ToCopy").Rows(k)) = 0 Then

          Sheets("Main").Rows(i).Copy
          Sheets("ToCopy").Select
          Rows(k).Select
          ActiveSheet.Paste
          'do not forget to exit for loop as it will fill all empty rows in 2nd sheet
          Exit For

      End If
    Next k

  End If

i = i + 1
Loop

End Sub

这篇关于搜索特定的单词并复制到另一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！