发生了很奇怪的事。在这一点上,这是有效的,突然之间,我得到了一个错误。
当调试打开时,问题是fm和sm,fm = Empty和sm = Empty。
因此,tval = Empty错误发生在第34行:
tval = WorksheetFunction.Sum(ws.Range(ws.Cells(fm, 11), ws.Cells(sm, 11)))
完整代码:
Private Sub TextBox6_Change()
Dim ws As Worksheet
Dim sr, leg, lr, fm, sm, tval, sval, i, j As Long
Dim wo, rc As String
Set ws = ThisWorkbook.Worksheets("Cost Analysis") ' ws = Work Sheet
sr = 5 ' sr = Start row
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row ' lr = Last row
wo = Trim(UserForm1.TextBox4.Value) ' Work Order
leg = Trim(UserForm1.TextBox5.Value) ' leg
rc = Trim(UserForm1.ComboBox3.Value) ' rc = Root Cause
If wo <> "" And leg <> "" And rc <> "" And TextBox6.Value <> "" Then
If Application.WorksheetFunction.CountIfs(ws.Cells(sr, 1).Resize(lr, 1), wo, _
ws.Cells(sr, 4).Resize(lr, 1), leg, _
ws.Cells(sr, 5).Resize(lr, 1), rc) > 0 Then
For i = sr To lr
If Trim(ws.Cells(i, 1)) = wo And Trim(ws.Cells(i, 4)) = leg Then
j = j + 1
If j = 1 Then
fm = i
End If
If Trim(ws.Cells(i, 5)) = rc Then
sm = i
Exit For
Else
End If
End If
Next
tval = WorksheetFunction.Sum(ws.Range(ws.Cells(fm, 11), ws.Cells(sm, 11)))
sval = WorksheetFunction.Sum(ws.Range(ws.Cells(fm, 11), ws.Cells(sm, 11))) * Trim(TextBox6.Value)
Else
End If
End If
UserForm1.TextBox7 = tval
UserForm1.TextBox16 = sval
End Sub发布于 2018-05-09 20:32:17
是否可能输入wo和leg无效?如果是的话
If Trim(ws.Cells(i, 1)) = wo And Trim(ws.Cells(i, 4)) = leg将不计算为True的任何i,然后fm和sm将是Empty。这将导致第34行抛出一个错误。
希望这能有所帮助。
https://stackoverflow.com/questions/50260509
复制相似问题