VBA Runtime Error 1004 “Application-defined or Object-defined error” with Range

Question

This is the first time that I am writing a macro by myself and I encountered two little problems.

Macro's task: Copy information of one document into another document in specific column, if the column is empty, in this case it should use the next column.

This is the code so far:

Sub CopyData()

    Dim i As Long
    Dim wbA As Workbook
    Dim wbN As Workbook
    Dim Filepath As String

    i = 7
    Set wbA = ThisWorkbook

    Filepath = "C:\Users\sebastian\Desktop\assessment answers"

    Do
        If IsEmpty(wbA.Sheets("Answers").Cells(1, i)) Then

            Set wbN = Workbooks.Open(Filepath)

            If Cells(37, 3).Value = 31 Then

                wbN.Sheets("Answers").Range(Cells(37, 4), Cells(46, 4)).Copy _
                Destination:=wbA.Sheets("Answers").Range(Cells(36, i), Cells(45, i))

            ElseIf Cells(37, 3).Value = 41 Then

                wbN.Sheets("Answers").Range(Cells(37, 4), Cells(46, 4)).Copy _
                Destination:=wbA.Sheets("Answers").Range(Cells(46, i), Cells(55, i))

            ElseIf Cells(37, 3).Value = 51 Then

                wbN.Sheets("Answers").Range(Cells(37, 4), Cells(46, 4)).Copy _
                Destination:=wbA.Sheets("Answers").Range(Cells(56, i), Cells(65, i))

            Else

                MsgBox "There could be a problem with the data, please check if the candidate has selected a topic."
                Exit Sub

            End If

            wbN.Sheets("Answers").Range(Cells(2, 4), Cells(3, 4)).Copy _
            Destination:=wbA.Sheets("Answers").Range(Cells(1, i), Cells(2, i))

            wbN.Sheets("Answers").Range(Cells(7, 4), Cells(36, 4)).Copy _
            Destination:=wbA.Sheets("Answers").Range(Cells(6, i), Cells(35, i))

            wbN.Close

            Exit Sub

        Else

            i = i + 1

        End If

    Loop

End Sub

1. The problem (VBA Runtime Error 1004) occurs here after: If Cells(37, 3).Value = 31 Then

If I use .Range("D37:D46") and Ranges for the other cells, it is working, but I would like to increase the column with the loop, when there is data filled in already. Do you have an idea to solve this?

2. Is there a way to change the filepath, so that who ever uses it, will be directed to the desktop, where the file should be located?

Filepath = "C:\Users\sebastian\Desktop\assessment answers"

Thank you for your ideas,

Sebastian

Re 1 this problem is covered in many questions here. You need to qualify the Cells calls with a worksheet too: ` wbN.Sheets("Answers").Range(wbN.Sheets("Answers").Cells(37, 4), wbN.Sheets("Answers").Cells(46, 4))`. Using a variable for the sheet or a With statement will tidy it up. — Rory
You need to identify Cells(37,3).Value is from which workbook and which sheet. I think, this can solve the error message. When I test your code, it show no error for me. — R.Katnaan
In response to problem 1: support.microsoft.com/en-us/help/210684/… Regarding Problem 2: You can reference desktop like this: Environ$("USERPROFILE") & "\Desktop" but I'm not sure how reliable it is, you're better to use thisworkbook.path if the file is on the desktop anyway — Preston
And for problem 2, you should ask file using file browser at the start of the program. So, user can put the file anywhere. No restriction for file place. — R.Katnaan

McPaddy McPaddy · Accepted Answer · 2017-02-10T11:53:56

Error number 1004 normally means "I can't find what you are looking for".

For this specific example you are seeing this error because you aren't being explicit enough with your coding. It is better to go OTT if anything.

So instead of

If Cells(37, 3).Value = 31 Then

Use:

If wbA.Sheets("Sheet Name Here").Cells(37, 3).Value = 31 Then

Even better include a 'With' statement in your code. A generic example is below:

    Sub Example()

    Dim wb As Workbook
    Dim sht As Worksheet

    Set wb = ActiveWorkbook
    Set sht = wb.ActiveSheet

    With sht
        .Name = "New Name"
        .Visible = xlSheetVisible
        .Protect
    End With

End Sub

This saves you a lot of typing and allows you to do multiple things to one object in one hit.

VBA Runtime Error 1004 “Application-defined or Object-defined error” with Range

2 Answers