编译错误：对象“_Global”的方法“范围”失败 - 搜索复制粘贴宏 Excel VBA

Compile Error: Method 'Range' of object '_Global' failed - Search Copy Paste Macro Excel VBA

提问by user2464111

I'm trying to make a macro in Excel VBA 2007 that searches through the selected field and if it finds a certain string anywhere in a row, it copies and pastes that row into another sheet.

我正在尝试在 Excel VBA 2007 中创建一个宏来搜索所选字段，如果它在一行中的任何位置找到某个字符串，它就会将该行复制并粘贴到另一个工作表中。

However, I'm getting the error in the title on the row noted below. What would be causing this?

但是，我在下面提到的行的标题中收到错误消息。什么会导致这种情况？

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
    numRows = Range(selectedRange).Rows.Count '<<<<<<<< Error
    numColumns = Range(selectedRange).Columns.Count

    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
    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
        End If
    Next rowNumber

End Sub

回答by David Zemens

Try:

尝试：

numRows = selectedRange.Rows.Count '<<<<<<<< Error
numColumns = selectedRange.Columns.Count

There may be other errors, I have not tested your full code, but this should fix the immediate error you're experiencing.

可能还有其他错误，我尚未测试您的完整代码，但这应该可以解决您遇到的即时错误。

回答by Ripster

Some tips:

一些技巧：

1. Declare all of your variables at the top of your sub
2. Add a new line for each variable to make your code more readable
3. Anytime you are using a variable to store row numbers declare it as Long
4. If you know the range you want to work with beforehand define it as a range in your code

1. 在子顶部声明所有变量
2. 为每个变量添加一个新行，使您的代码更具可读性
3. 每当您使用变量存储行号时，将其声明为 Long
4. 如果您事先知道要使用的范围，请将其定义为代码中的范围

This code should do something close to what you want. Give it a try and let me know. If you know the range you would like to use before running the macro instead of using "Selection" I suggest specifying the exact range or "Sheets(1).UsedRange" for the entire first sheet.

这段代码应该做一些接近你想要的事情。试一试，让我知道。如果您在运行宏之前知道要使用的范围而不是使用“选择”，我建议为整个第一张纸指定确切的范围或“Sheets(1).UsedRange”。

Sub SearchCopyPaste()
    Dim fnd As String
    Dim vCell As Range
    Dim rng As Range
    Dim totalCols As Integer
    Dim rowCounter As Long

    'Set this to a specific range if possible
    Set rng = Selection
    totalCols = rng.Columns.Count

    'Get the data to find from the user
    fnd = InputBox("Input data to find")

    'Loop through all cells in the selected range
    For Each vCell In rng
        'If the data is found copy the data and paste it to Sheet2, move down one row each time
        If InStr(vCell.Value, fnd) > 0 Then
            rowCounter = rowCounter + 1
            Range(Cells(vCell.row, 1), Cells(vCell.row, totalCols)).Copy Destination:=Sheets(2).Cells(rowCounter, 1)
        End If
    Next

    'Copy the column headers onto the second sheet
    Sheets(2).Rows(1).EntireRow.Insert
    rng.Range(Cells(1, 1), Cells(1, totalCols)).Copy Destination:=Sheets(2).Cells(1, 1)
End Sub