我正在尝试在Excel VBA 2007中创建一个搜索所选字段的宏，如果它在一行中的任何位置找到某个字符串，它会将该行复制并粘贴到另一个工作表中 .
但是，我收到了一个编译错误：在子级别上需要对象（第1行） . 到目前为止，我的代码如下 .
Sub SearchCopyPaste()
'
' SearchCopyPaste Macro
' Searches for a string. If it finds that string in the line of a document then it copies and pastes it into a new worksheet.
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Dim sourceSheet, destinationSheet As Worksheet
Set sourceSheet = Worksheets(1) 'Define worksheets
Set destinationSheet = Worksheets(2)
Dim selectedRange As Range 'Define source range
Set selectedRange = Selection
Dim numRows, numColumns As Integer 'Determine how many rows and columns are to be searched
Set numRows = Range(selectedRange).Rows.Count
Set numColumns = Range(selectedRange).Columns.Count
Set destinationRowCount = 1 'Counter to see how many lines have been copied already
'Used to not overwrite, can be modified to add header,etc
Dim searchString As String 'String that will be searched. Will eventually be inputted
Set searchString = "bccs" 'Will eventually be put into msgbox
For rowNumber = 1 To numRows
If InStr(1, selectedRange.Cells(i, numColumns), searchString) > 0 Then
selectedRange.Cells(rowNumber, numColumns).Copy Destination:=destinationSheet.Range(Cells(destinationRowCount, numColumns))
destinationRowCount = destinationRowCount + 1
Next rowNumber
End Sub
1 回答
您使用
Set关键字错误 . 在上面的代码中，您只需要使用它来分配Worksheet和Range对象，而不是整数和字符串 . 删除这些行上的Set关键字 .此外，您在
For循环中缺少End If.