Excel VBA范围单元格错误对象已定义 [英] excel vba range cell error object defined

问题描述

我一直在处理Excel中的宏，该宏对一张工作表中的表(次要")进行排序，当满足条件时，应该将该表中的数据添加到第二张工作表中的另一个数据("Sheet1" )，但我始终遇到1004错误未定义对象"，并且我不明白为什么，请提供帮助.谢谢！

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

推荐答案

设置以下值时，您缺少范围引用:

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

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

尝试一下:

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

这篇关于Excel VBA范围单元格错误对象已定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！