VBA - 未知的错误原因 [英] VBA - Unknown Reason for Error

问题描述

我收到一个对象变量或块方差未设置错误，原因我无法确定。使用错误处理程序，VBA代码似乎按照预期工作。我想解决错误，而不是跳过它。
任何人都可以指出这个错误发生在哪里/为什么？还是其他有关此代码的建议？任何帮助不胜感激！

I am getting an 'Object variable or With block variance not set' error for a reason I cannot figure out. With the Error Handler in place, the VBA code appears to work as intended. I would like to resolve the error, rather than skipping over it. Can anyone point out where/why this error is occurring? Or any other suggestions for this code? Any help is appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tx As Range
Dim Rw As Variant

On Error GoTo E_H 'If this error handler is not in effect, and an edit is
                  '    made in column D, the error occurs.

Set Tx = Range("E44")
Set Rw = Rows("47")

If Application.Intersect(Tx, Range(Target.Address)).Value = "x" Then
    Application.EnableEvents = False
        With Range("C45")
            .Value = "T - 10"
        End With
        With Range("C45").Characters(Start:=36, Length:=5).Font
            .Color = -16776961
        End With
        With Range("I45")
            .Value = "T - 10 - LOS"
        End With
    Rw.Hidden = False
        With Range("B48")
            .Formula = "=B47+1"
        End With
    Sheets("DropDowns").Range("M6").Value = "65"
    Application.EnableEvents = True

Else

    Application.EnableEvents = False
        With Range("C45")
        .Value = "25Ac"
        End With
            With Range("I45")
            .Value = "25Ac - LOS"
        End With
    Rw.Hidden = True
        With Range("B48")
            .Formula = "=B46+1"
        End With
    Sheets("DropDowns").Range("M6").Value = "64"        
    Application.EnableEvents = True
End If

E_H: 
    Application.EnableEvents = True
    Exit Sub

End Sub

推荐答案

问题似乎在这一行。

If Application.Intersect(Tx, Range(Target.Address)).Value = "x" Then
'better and more succinct as,
If Application.Intersect(Tx, Target).Value = "x" Then

你不知道目标相交 Range（E44），但您尝试从交集中获取值，而无需确定是否有实际值得到的值。

You do not know if Target intersects Range("E44") but you are trying to get the value from the intersection without determining if there is actually anything to get the value from.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("E44"), Target) Is Nothing Then
        On Error GoTo E_H
        Application.EnableEvents = False

        If LCase(Range("E44").Value) = "x" Then
            With Range("C45")
                .Value = "T - 10"
                .Characters(Start:=36, Length:=5).Font.Color = -16776961
            End With
            Range("I45") = "T - 10 - LOS"
            Range("B48").Formula = "=B47+1"
            Rows("47").EntireRow.Hidden = False
            Worksheets("DropDowns").Range("M6").Value = 65
        Else
            Range("C45") = "I45"
            Range("I45") = "25Ac - LOS"
            Range("B48").Formula = "=B46+1"
            Rows("47").EntireRow.Hidden = True
            Worksheets("DropDowns").Range("M6").Value = 64
        End If
    End If

E_H:
    Application.EnableEvents = True

End Sub

我不知道我的逻辑流程是否正确，但这是我最好的猜测。如果E44发生变化，现在是 x ，取消隐藏第47行，调整公式等。如果E44发生变化，而不是 x 然后隐藏第47行，调整公式等。

I'm not sure I got your logic flow correct but this is my best guess. If E44 changes and it is now an x, unhide row 47, adjust a formula, etc. If E44 changes and it is not an x then hide row 47, adjust a formula, etc.

这篇关于VBA - 未知的错误原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！