excel vba range cell error object defined

Question

I've been working on a macro in Excel that sorts through a table in one sheet ("Minor") and when a criteria is met it should add data from that table to another one in a second sheet ("Sheet1"), but I keep running with the 1004 error "object not defined" and I don't understand why, please help. Thanks!

Sub ord_esp_aprob()
  a = Worksheets("Minor").Cells(Rows.Count, 1).End(xlUp).Row
  b = Worksheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row + 1

  For i = 3 To a
    If Worksheets("Minor").Cells(i, 1).Value = "Orden en Espera de Aprobación" Then
      Worksheets("Sheet1").Range(Cells(b, 4)).Value = Worksheets("Minor").Range(Cells(i, 2)).Value '(This is where the error occurs)
      Worksheets("Sheet1").Activate
    End If
  Next i
End Sub

warner_sc · Accepted Answer · 2018-05-07T22:52:09.260

0

You're missing range references when setting the value of:

Worksheets("Sheet1").Range(Cells(b, 4)).Value

Try this:

Option Explicit

Sub ord_esp_aprob()

Dim a As Long
Dim b As Long
Dim i As Long

Dim sht_minor As Worksheet
Dim sht_sht1 As Worksheet

Set sht_minor = ThisWorkbook.Worksheets("Minor")
Set sht_sht1 = ThisWorkbook.Worksheets("Sheet1")

a = sht_minor.Cells(sht_minor.Rows.Count, 1).End(xlUp).Row
b = sht_sht1.Cells(sht_sht1.Rows.Count, 4).End(xlUp).Row + 1

For i = 3 To a

If sht_minor.Cells(i, 1).Value = "Orden en Espera de Aprobación" Then
    sht_sht1.Cells(b, 4).Value = sht_minor.Cells(i, 2).Value
    sht_sht1.Activate
End If

Next i

End Sub

edited May 07 '18 at 22:52

answered May 07 '18 at 22:45

warner_sc

848
6
13

Hi and thanks! I just tried it but it still gives me the same error... If i change Cells( , ) for a fixed range like "A7" it works so i guess the problem is in the synthax of Cells( , ) but i used it that way before and in worked.. so im clueless. – Dario Sanchez May 07 '18 at 22:50
Updated the code, can you try again please.. – warner_sc May 07 '18 at 22:52
Let me know if the last update works.. – warner_sc May 07 '18 at 22:56
That does it, thanks! Although i still don't understand why the method i was using didn't work. – Dario Sanchez May 07 '18 at 22:56
Check my code, you can see that you dont really need to use the `Range` method and then `Cells`, also try referencing ALL your ranges, look at the code, all range references start from the actual workbook, then the sheet name and finally the range! – warner_sc May 07 '18 at 22:59
Please mark my answer and vote if you find it helpfull – warner_sc May 07 '18 at 22:59
I see thanks i'll keep it in mind! – Dario Sanchez May 07 '18 at 23:08

excel vba range cell error object defined

1 Answers1

Linked

Related