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屋!