Application-Defined or Object-Defined Error Excel

Question

I have this sub in Excel 2010 that’s supposed to do the following:

· Take the value in (6, 4) in Sheet1(Form) and find this value in Sheet7’s (Dates) column 1

· Find the row in which it finds this match

· Find the value at Sheet7(row, 6)

· Paste it into Sheet1(19, 5)

Sheet1 is titled Form, and Sheet7 is titled Dates.

This is the code that I’ve written. When I try to run it, it gives me a run-time error ‘1004: Application-defined or object-defined error’ at Sheets("Dates")... Any assistance would be greatly appreciated.

 Option Explicit

Private Sub btnNext_Click()
        Dim ProjNo As String
        Dim ProjRow As Long
        Dim Found As Range   

    ProjNo = Worksheets("Form").Cells(6, 4).Value        

         Set Found = Sheets("Dates").Columns(1).Find(what:=ProjNo, LookIn:=xlValues, lookat:=xlWhole)

          If Found Is Nothing Then   
              MsgBox "Project not found."    
              EnterProj.Show    
         Else   
             ProjRow = Found.Row    
        End If        

          Sheets("Dates").Range(ProjRow, 6).Copy Destination:=Sheets("Form").Range(19, 5)    

End Sub

'Sheets("Dates").Range(ProjRow, 6).Copy Destination:=Sheets("Form").Range(19, 5)' , remove the qoutes — mrbungle
I suggest you update the code with what you now have based on the corrections already suggested. — Rory

Ravi Yenugu Ravi Yenugu · Accepted Answer · 2014-07-22T15:35:51

You are not using the Range Object Correctly,

For e.g.

.Range(2,6)<>Cells(2,6), Range parameters has to be A1-style reference or other styles

Replace

Sheets("Dates").Range(ProjRow, 6).Copy Destination:=Sheets("Form").Range(19, 5)

with

Sheets("Form").Cells(19, 5)=  Sheets("Dates").Cells(ProjRow, 6)

Application-Defined or Object-Defined Error Excel

1 Answers