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

0
votes

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.

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
2
vbaexcel
Would it not be easier just to filter for the string and copy everything at once? - Ripster
This could well be the case. However, I don't know exactly how to code that in VBA (fairly beginner). Do you have any ideas? Also just to add, some of the strings that I will be looking for are not the sole contents of the cell (i.e. it could be "Smith, John / Jones, Jane" and would just be searching for Jones). Could this be solved by filtering? - user2464111

2 Answers

1
votes

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.

0
votes

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

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.

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