0

I am using Excel 2010 to write a VBA script where I want to copy values from one sheet ("Database") to another sheet ("Search") given that a certain criteria, fulfilled by an IF Statement, is verified.

I've created a module where I've written the following code:

  Sub searchdatabase()

'1. declare variables
'2. clear old search results
'3. Find records that match criteria and paste them

Dim country As String
Dim category As String
Dim subcategory As String
Dim finalrow As Integer
Dim i As Integer 'row counter

Sheets("Search").Range("E11:P1000").ClearContents 'E11:P1000 e o range de celulas que ele vai apagar antes de correr o script. Se quiser mudar ou adicionar headers, tenho de mudar este range tambem.

country = Sheets("Search").Range("E5").Value
finalrow = Sheets("Database").Range("A200000").End(xlUp).Row

For i = 2 To finalrow
    If Sheets("Database").Cells(i, 1) = country Then
        Sheets("Database").Range(Cells(i, 1), Cells(i, 9)).Copy
        Sheets("Search").Range("E600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

        End If

Next i

End Sub

In the IF Statement, I have referenced that I want to see if the variable 'Country' matches the Cells in the "Database" sheet and, if so, copy it to the "Search" sheet. But the code seems not to be working, returning error

Run-time error '1004':
Application-defined or object-defined error

In order to solve the issue I have tried to change the code and reference different sheets but the error seems to be related to this still.

What am I doing wrong?

Thank you

franciscofcosta
  • 833
  • 5
  • 25
  • 53

1 Answers1

3

The problem is with this line:

Sheets("Database").Range(Cells(i, 1), Cells(i, 9)).Copy

When you run it from the Search spreadsheet, it interprets "Cells" to mean Cells from "Search". Use this instead:

    With Sheets("Database")
        .Range(.Cells(i, 1), .Cells(i, 9)).Copy
    End With
jeff carey
  • 2,313
  • 3
  • 13
  • 17
  • For a little extra info, when you *don't* qualify a range (`Range()`,`Cells()`,`Rows()`,`Columns()`, etc.) with the spreadsheet, VBA will assume you want that run on whatever the `ActiveSheet` is. OP, you're getting an error, because the `Sheets("Search")` is the active sheet, but you're using `Sheets("Database")` to 'introduce' the range, and that conflicts. – BruceWayne Nov 17 '16 at 00:34