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
– mrbungle, Commented Jul 22, 2014 at 15:20
I suggest you update the code with what you now have based on the corrections already suggested. — Rory
– Rory, Commented Jul 22, 2014 at 15:24

Ravi Yenugu · Accepted Answer · 2014-07-22 16:01:31Z

5

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)

edited Jul 22, 2014 at 16:01

answered Jul 22, 2014 at 15:35

Ravi Yenugu

3,8885 gold badges43 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AxxieD Over a year ago

It didn't give me an error, but it also didn't paste the value from the one sheet to the other.

Mark Balhoff Over a year ago

How is the second line performing the desired task? The first line (if you swapped Range for Cells) is trying to copy from projRow to row 19. You are assigning to projRow instead of row 19

Ravi Yenugu Over a year ago

Oops My bad I flipped the source and destination, I updated the answer

Mark Balhoff Over a year ago

All he really needed to do to get the code working was flip Range for Cells in both instances on that line and then it works on my machine. Yours is better though since it sounds like all he wants in this case is the value.

Collectives™ on Stack Overflow

Application-Defined or Object-Defined Error Excel

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related