使用Range.Offset时，VBA运行时错误1004“应用程序定义的错误或对象定义的错误" [英] VBA Runtime Error 1004 “Application-defined or Object-defined error” when using Range.Offset

问题描述

使用Range.Offset选择范围时，出现运行时错误1004应用程序定义的错误或对象定义的错误"的问题.我的许多代码都包含 If 和 ElseIf ，而我只更改了值，甚至没有触摸这些代码.在更改 IF 语句中的值之前，VBA正常工作.现在，它一直给我运行时错误".

I'm having an issue with Runtime Error 1004 "Application-defined or Object-defined error" when Selecting Range using Range.Offset. A lot of my codes contain If and ElseIf and I only changed the values, did not even touch the codes. The VBA worked just fine before I changed the values in IF statements. Now it keeps giving me Runtime Error.

这是我的密码.但是它们很长.

Here are my codes. They are very long, though.

Sub compare2()

Dim i As Long
Dim A As Long
Dim B As Long
Dim c As Long

A = 14
B = 15
c = 16



Do While A <= 42
    i = 2
    Do Until Len(Cells(i, A)) = 0

        If Cells(i, A) = "Green" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Green"

        ElseIf Cells(i, A) = "Green" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Green"

        ElseIf Cells(i, A) = "Green" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Green" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Green" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Rollup"

        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Green"

        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Available"

        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Available"

        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Available"

        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Available"

        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Outside" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Outside"

         ElseIf Cells(i, A) = "Outside" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Outside"

        ElseIf Cells(i, A) = "Outside" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Outside"

        ElseIf Cells(i, A) = "Outside" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Outside"



        ElseIf Cells(i, A) = "Podding" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Rollup"



        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Title Transfer"

        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Title Transfer"

        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Title Transfer"

        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Title Transfer"

        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = " " And Cells(i, B) = " " Then
        Cells(i, c) = " "

        Else


        End If
        i = i + 1
    Loop



    A = A + 4
    B = A + 1
    c = A + 2
Loop


End Sub

第二个:

Public Sub single_change(changed_cell As Range)


    Dim sales_cell As Range
    Dim production_cell As Range
    Dim day_cell As Range

    If changed_cell.Column Mod 2 = 0 Then
        Set sales_cell = changed_cell
        Set production_cell = changed_cell.Offset(, 1)
        Set day_cell = production_cell.Offset(, 1)
    Else
        Set production_cell = changed_cell
        Set sales_cell = changed_cell.Offset(, -1)
        Set day_cell = production_cell.Offset(, 1)
    End If


    On Error GoTo multiple_changes

    If sales_cell = "Green" And production_cell = "Rollup" Then
        day_cell = "Green"

        ElseIf sales_cell = "Green" And production_cell = "Green" Then
        day_cell = "Green"

        ElseIf sales_cell = "Green" And production_cell = "Yellow" Then
       day_cell = "Yellow"

        ElseIf sales_cell = "Green" And production_cell = "Red" Then
        day_cell = "Red"

        ElseIf sales_cell = "Green" And production_cell = "Overdue" Then
       day_cell = "Overdue"



        ElseIf sales_cell = "Yellow" And production_cell = "Rollup" Then
        day_cell = "Yellow"

        ElseIf sales_cell = "Yellow" And production_cell = "Green" Then
        day_cell = "Yellow"

        ElseIf sales_cell = "Yellow" And production_cell = "Yellow" Then
        day_cell = "Yellow"

        ElseIf sales_cell = "Yellow" And production_cell = "Red" Then
        day_cell = "Red"

        ElseIf sales_cell = "Yellow" And production_cell = "Overdue" Then
        day_cell = "Overdue"



        ElseIf sales_cell = "Red" And production_cell = "Rollup" Then
        day_cell = "Red"

        ElseIf sales_cell = "Red" And production_cell = "Green" Then
        day_cell = "Red"

        ElseIf sales_cell = "Red" And production_cell = "Yellow" Then
       day_cell = "Red"

        ElseIf sales_cell = "Red" And production_cell = "Red" Then
        day_cell = "Red"

        ElseIf sales_cell = "Red" And production_cell = "Overdue" Then
        day_cell = "Overdue"



        ElseIf sales_cell = "Rollup" And production_cell = "Rollup" Then
        day_cell = "Rollup"

        ElseIf sales_cell = "Rollup" And production_cell = "Green" Then
        day_cell = "Green"

        ElseIf sales_cell = "Rollup" And production_cell = "Yellow" Then
        day_cell = "Yellow"

        ElseIf sales_cell = "Rollup" And production_cell = "Red" Then
        day_cell = "Red"

        ElseIf sales_cell = "Rollup" And production_cell = "Overdue" Then
        day_cell = "Overdue"



        ElseIf sales_cell = "Available" And production_cell = "Rollup" Then
        day_cell = "Available"

        ElseIf sales_cell = "Available" And production_cell = "Green" Then
        day_cell = "Available"

        ElseIf sales_cell = "Available" And production_cell = "Yellow" Then
        day_cell = "Available"

        ElseIf sales_cell = "Available" And production_cell = "Red" Then
        day_cell = "Available"

        ElseIf sales_cell = "Available" And production_cell = "Overdue" Then
        day_cell = "Overdue"



        ElseIf sales_cell = "Outside" And production_cell = "Rollup" Then
        day_cell = "Outside"

        ElseIf sales_cell = "Outside" And production_cell = "Green" Then
        day_cell = "Outside"

        ElseIf sales_cell = "Outside" And production_cell = "Yellow" Then
        day_cell = "Outside"

        ElseIf sales_cell = "Outside" And production_cell = "Red" Then
        day_cell = "Outside"



        ElseIf sales_cell = "Podding" And production_cell = "Rollup" Then
        day_cell = "Rollup"



        ElseIf sales_cell = "Title Transfer" And production_cell = "Rollup" Then
        day_cell = "Title Transfer"

        ElseIf sales_cell = "Title Transfer" And production_cell = "Green" Then
        day_cell = "Title Transfer"

        ElseIf sales_cell = "Title Transfer" And production_cell = "Yellow" Then
        day_cell = "Title Transfer"

        ElseIf sales_cell = "Title Transfer" And production_cell = "Red" Then
       day_cell = "Title Transfer"

        ElseIf sales_cell = "Title Transfer" And production_cell = "Overdue" Then
        day_cell = "Overdue"



        ElseIf sales_cell = " " And production_cell = " " Then
        day_cell = " "


        Else

        End If

        Exit Sub

multiple_changes:

Dim i As Long
Dim A As Long
Dim B As Long
Dim c As Long

A = 14
B = 15
c = 16



Do While A <= 42
    i = 2
    Do Until Len(Cells(i, A)) = 0

        If Cells(i, A) = "Green" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Green"

        ElseIf Cells(i, A) = "Green" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Green"

        ElseIf Cells(i, A) = "Green" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Green" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Green" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Yellow" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Red" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Rollup"

        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Green"

        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Yellow"

        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Red"

        ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Available"

        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Available"

        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Available"

        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Available"

        ElseIf Cells(i, A) = "Available" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



         ElseIf Cells(i, A) = "Outside" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Outside"

        ElseIf Cells(i, A) = "Outside" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Outside"

        ElseIf Cells(i, A) = "Outside" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Outside"

        ElseIf Cells(i, A) = "Outside" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Outside"



        ElseIf Cells(i, A) = "Podding" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Rollup"



        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Rollup" Then
        Cells(i, c) = "Title Transfer"

        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Green" Then
        Cells(i, c) = "Title Transfer"

        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Yellow" Then
        Cells(i, c) = "Title Transfer"

        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Red" Then
        Cells(i, c) = "Title Transfer"

        ElseIf Cells(i, A) = "Title Transfer" And Cells(i, B) = "Overdue" Then
        Cells(i, c) = "Overdue"



        ElseIf Cells(i, A) = " " And Cells(i, B) = " " Then
        Cells(i, c) = " "

        Else


        End If
        i = i + 1

        Loop



    A = A + 4
    B = A + 1
    c = A + 2

    Loop

End Sub

我不确定为什么以前的同事会多次重复这些 If 和 ElseIf 条件.请告诉我如何改进这些代码.

I'm not sure why my former colleagues repeat those If and ElseIf conditions many times. Please advise me howI can improve these codes.

另外，当我单击debug时，第二批中的 Set sales_cell = changed_cell.Offset(，-1)一直突出显示.

Also, when I clicked debug, the Set sales_cell = changed_cell.Offset(, -1) in the 2nd batch kept being highlighted.

一些额外的信息，我的sales_cell是第14列(第N列)，production_cell是第15列(第O列).

Some extra info, my sales_cell is the 14th column (column N), production_cell is the 15th column (column O).

这是我应该使用宏的工作表中的内容:

This is what I have in the sheet that is supposed to use Macro:

Private Sub Worksheet_Change(ByVal Target As Range)

    Call Module1.single_change(Target)

End Sub

这是宏应如何工作的(但事实并非如此).我有一组4个相邻的列:N列(或Sales)，O列(或Production)，P列(称为Day#)和Q列(称为Status).无论用户在N列(销售)和M列(生产)中放置/更改任何内容，宏都会从 If 语句中返回相应的值.并将相同的方式应用于接下来的4列，依此类推.

Here is how the Macro should work (but it doesn't though). I have a set of 4 adjacent columns: Column N (or Sales), Column O (or Production), Column P (known as Day#), and Column Q (known as Status). the way it works is whatever the user puts/changes in Column N (Sales) and Column M (Production), the macro will return the corresponding value from the If statements. And that applies the same way to the next 4 columns and so on.

非常感谢您的帮助.我一直困扰着这个问题好几天了，但仍然不知道问题到底是什么.

Any help is very much appreciated. I've stuck with this problem for days and still have no idea what the issue really is.

谢谢！

推荐答案

只要工作簿中的任何单元格发生更改，就好像您都在调用它.
然后，它尝试在已更改的单元格及其旁边的单元格上运行代码.
这意味着，如果您更改B列，B列，C列和D列中的内容，则会受到影响.

It looks as though you call it whenever ANY cell is changed in your workbook.
Then it tries to run the code on that changed cell and the cells beside it.
This means if you change something in Column B, Column B, C, and D would be affected.

如果我们将其更新为仅在更改N/R/V或O/S/W列或之后的任何重复列时才进行更改，则您将不再收到错误.

If we update it to only change if you change column N/R/V or O/S/W, or any repeated columns after that, then you should no longer receive the error.

尝试将所有单次更改"更新为:

Try updating all of "single_change" to:

Public Sub single_change(changed_cell As Range)

Dim sales_cell As Range
Dim production_cell As Range
Dim day_cell As Range
Dim col_num as Integer

col_num = changed_cell.Column
If changed_cell.Column < 14 then 'Dont do anything before Col N
    Exit Sub
Else
    col_num = changed_cell.Column - 14
End if

If col_num  Mod 4 = 0 Then
    Set sales_cell = changed_cell
    Set production_cell = changed_cell.Offset(, 1)
    Set day_cell = production_cell.Offset(, 2)
ElseIf (col_num - 1)  Mod 4 = 0 Then
    Set sales_cell = changed_cell.Offset(, -1)
    Set production_cell = changed_cell
    Set day_cell = production_cell.Offset(, 1)
Else
    'Dont do anything between Col N,O and their repeated values
    Exit Sub
End If

If sales_cell = "Green" And production_cell = "Rollup" Then
    day_cell = "Green"
ElseIf sales_cell = "Green" And production_cell = "Green" Then
    day_cell = "Green"
ElseIf sales_cell = "Green" And production_cell = "Yellow" Then
   day_cell = "Yellow"
ElseIf sales_cell = "Green" And production_cell = "Red" Then
    day_cell = "Red"
ElseIf sales_cell = "Green" And production_cell = "Overdue" Then
   day_cell = "Overdue"
ElseIf sales_cell = "Yellow" And production_cell = "Rollup" Then
    day_cell = "Yellow"
ElseIf sales_cell = "Yellow" And production_cell = "Green" Then
    day_cell = "Yellow"
ElseIf sales_cell = "Yellow" And production_cell = "Yellow" Then
    day_cell = "Yellow"
ElseIf sales_cell = "Yellow" And production_cell = "Red" Then
    day_cell = "Red"
ElseIf sales_cell = "Yellow" And production_cell = "Overdue" Then
    day_cell = "Overdue"
ElseIf sales_cell = "Red" And production_cell = "Rollup" Then
    day_cell = "Red"
ElseIf sales_cell = "Red" And production_cell = "Green" Then
    day_cell = "Red"
ElseIf sales_cell = "Red" And production_cell = "Yellow" Then
   day_cell = "Red"
ElseIf sales_cell = "Red" And production_cell = "Red" Then
    day_cell = "Red"
ElseIf sales_cell = "Red" And production_cell = "Overdue" Then
    day_cell = "Overdue"
ElseIf sales_cell = "Rollup" And production_cell = "Rollup" Then
    day_cell = "Rollup"
ElseIf sales_cell = "Rollup" And production_cell = "Green" Then
    day_cell = "Green"
ElseIf sales_cell = "Rollup" And production_cell = "Yellow" Then
    day_cell = "Yellow"
ElseIf sales_cell = "Rollup" And production_cell = "Red" Then
    day_cell = "Red"
ElseIf sales_cell = "Rollup" And production_cell = "Overdue" Then
    day_cell = "Overdue"
ElseIf sales_cell = "Available" And production_cell = "Rollup" Then
    day_cell = "Available"
ElseIf sales_cell = "Available" And production_cell = "Green" Then
    day_cell = "Available"
ElseIf sales_cell = "Available" And production_cell = "Yellow" Then
    day_cell = "Available"
ElseIf sales_cell = "Available" And production_cell = "Red" Then
    day_cell = "Available"
ElseIf sales_cell = "Available" And production_cell = "Overdue" Then
    day_cell = "Overdue"
ElseIf sales_cell = "Outside" And production_cell = "Rollup" Then
    day_cell = "Outside"
ElseIf sales_cell = "Outside" And production_cell = "Green" Then
    day_cell = "Outside"
ElseIf sales_cell = "Outside" And production_cell = "Yellow" Then
    day_cell = "Outside"
ElseIf sales_cell = "Outside" And production_cell = "Red" Then
    day_cell = "Outside"
ElseIf sales_cell = "Podding" And production_cell = "Rollup" Then
    day_cell = "Rollup"
ElseIf sales_cell = "Title Transfer" And production_cell = "Rollup" Then
    day_cell = "Title Transfer"
ElseIf sales_cell = "Title Transfer" And production_cell = "Green" Then
    day_cell = "Title Transfer"
ElseIf sales_cell = "Title Transfer" And production_cell = "Yellow" Then
    day_cell = "Title Transfer"
ElseIf sales_cell = "Title Transfer" And production_cell = "Red" Then
   day_cell = "Title Transfer"
ElseIf sales_cell = "Title Transfer" And production_cell = "Overdue" Then
    day_cell = "Overdue"
ElseIf sales_cell = " " And production_cell = " " Then
    day_cell = " "
Else
    'Do nothing
End If
End Sub

希望有帮助:)

这篇关于使用Range.Offset时，VBA运行时错误1004“应用程序定义的错误或对象定义的错误"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！