Excel VBA错误代码91：在代码运行且未进行任何编辑之后，“对象变量或未设置块变量”

我在VBA中创建了一个宏，用于按星期跟踪条形码。这个想法是当条形码被扫描一次时，时间将进入下一个单元格（这是用公式完成的，效果很好），在一天结束时，材料必须被扫描回来，所以我为每天的顶部做了一个宏（星期一在单元格A1中）。这个想法是当条形码被扫描到单元格A1中时，它将搜索指定行（在我的情况下是B行）中的数据，当它找到该条目时，它将在时间戳下放置一个超时戳。
这段代码一直在工作，我保存了Excel文件，而我等待我的材料到达，所以现在，他们在这里，我想做最后一次测试运行，它不工作，我得到的错误，虽然我没有改变任何东西.我在下面附上了我的代码，并标记了我收到错误消息的位置。
任何帮助是非常感谢，我卡住了！
代码：

Private Sub worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
        Call monday
        Application.EnableEvents = True
    End If
If Not Intersect(Target, Me.Range("F1")) Is Nothing Then
        Call tuesday
        Application.EnableEvents = True
    End If
If Not Intersect(Target, Me.Range("K1")) Is Nothing Then
        Call wednesday
        Application.EnableEvents = True
    End If
If Not Intersect(Target, Me.Range("P1")) Is Nothing Then
        Call thursday
        Application.EnableEvents = True
    End If
If Not Intersect(Target, Me.Range("U1")) Is Nothing Then
        Call friday
        Application.EnableEvents = True
    End If
End Sub

Sub monday()
Dim barcode As String
Dim rng As Range
Dim foundval As Range
Dim diff As Double

Dim rownumber As Long

barcode = ActiveSheet.Cells(1, 1)
If barcode <> "" Then
    Set rng = ActiveSheet.Range("b5:b500").Find(what:=barcode, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If rng Is Nothing Then
        ActivrSheet.Cells(1, 1) = ""
    Else
        rownumber = rng.Row
        ActiveSheet.Range(Cells(rownumber, 1), Cells(rownumber, 4)).Find("").Select #ERROR MESSAGE HERE!
        ActiveCell.Value = Time
        ActiveCell.NumberFormat = "h:mm AM/PM"
        ActiveSheet.Cells(1, 1) = ""
    End If
End If
ActiveSheet.Cells(1, 1).Select
End Sub

Sub tuesday()
Dim barcode As String
Dim rng As Range
Dim foundval As Range
Dim diff As Double

Dim rownumber As Long

barcode = ActiveSheet.Cells(1, 6)
If barcode <> "" Then
    Set rng = ActiveSheet.Range("g:g").Find(what:=barcode, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If rng Is Nothing Then
        ActiveSheet.Cells(1, 6) = ""
    Else
        rownumber = rng.Row
        ActiveSheet.Range(Cells(rownumber, 6), Cells(rownumber, 9)).Find("").Select
        ActiveCell.Value = Time
        ActiveCell.NumberFormat = "h:mm AM/PM"
        ActiveSheet.Cells(1, 6) = ""
    End If
End If
ActiveSheet.Cells(1, 6).Select
End Sub

Sub wednesday()
Dim barcode As String
Dim rng As Range
Dim foundval As Range
Dim diff As Double

Dim rownumber As Long

barcode = ActiveSheet.Cells(1, 11)
If barcode <> "" Then
    Set rng = ActiveSheet.Range("l:l").Find(what:=barcode, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If rng Is Nothing Then
        ActiveSheet.Cells(1, 11) = ""
    Else
        rownumber = rng.Row
        ActiveSheet.Range(Cells(rownumber, 11), Cells(rownumber, 14)).Find("").Select
        ActiveCell.Value = Time
        ActiveCell.NumberFormat = "h:mm AM/PM"
        ActiveSheet.Cells(1, 11) = ""
    End If
End If
ActiveSheet.Cells(1, 11).Select
End Sub

Sub thursday()
Dim barcode As String
Dim rng As Range
Dim diff As Double

Dim rownumber As Long

barcode = ActiveSheet.Cells(1, 16)
If barcode <> "" Then
    Set rng = ActiveSheet.Range("q:q").Find(what:=barcode, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If rng Is Nothing Then
        ActiveSheet.Cells(1, 16) = ""
    Else
        rownumber = rng.Row
        ActiveSheet.Range(Cells(rownumber, 16), Cells(rownumber, 19)).Find("").Select
        ActiveCell.Value = Time
        ActiveCell.NumberFormat = "h:mm AM/PM"
        ActiveSheet.Cells(1, 16) = ""
    End If
End If
ActiveSheet.Cells(1, 16).Select
End Sub

Sub friday()
Dim barcode As String
Dim rng As Range
Dim diff As Double

Dim rownumber As Long

barcode = ActiveSheet.Cells(1, 21)
If barcode <> "" Then
    Set rng = ActiveSheet.Range("v:v").Find(what:=barcode, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If rng Is Nothing Then
        ActiveSheet.Cells(1, 21) = ""
    Else
        rownumber = rng.Row
        ActiveSheet.Range(Cells(rownumber, 21), Cells(rownumber, 24)).Find("").Select
        ActiveCell.Value = Time
        ActiveCell.NumberFormat = "h:mm AM/PM"
        ActiveSheet.Cells(1, 21) = ""
    End If
End If
ActiveSheet.Cells(1, 21).Select
End Sub

参见A1，其中将扫描条形码x1c 0d1x错误Error message

您的代码可以更短，因为它的大部分是重复的，唯一的变量是要搜索的扫描条形码的列。

Private Sub worksheet_Change(ByVal Target As Range)

    Dim cBarcode As Range, barCode, f As Range, c As Range, rngSrch As Range

    If Target.Cells.Count > 1 Then Exit Sub

    'data was changed in one of the cells we're monitoring?
    Set cBarcode = Application.Intersect(Target, _
                       Me.Range("A1,F1,K1,P1,U1"))
    If cBarcode Is Nothing Then Exit Sub

    barCode = cBarcode.Value
    If Len(barCode) = 0 Then Exit Sub

    'locate barcode in the column to the right of cBarcode
    Set f = cBarcode.Offset(0, 1).EntireColumn.Find( _
             what:=barCode, LookIn:=xlFormulas, LookAt:=xlWhole, _
             SearchOrder:=xlByRows, SearchDirection:=xlNext, _
             MatchCase:=False, SearchFormat:=False)

    If Not f Is Nothing Then       'found a barcode match?
        Set rngSrch = Me.Cells(f.row, cBarcode.Column).Resize(1, 4) 'fixed from 3...
        Set c = rngSrch.Find("")
        If Not c Is Nothing Then   'any empty cell?
            c.NumberFormat = "h:mm AM/PM"
            c.Value = Time
        Else
            MsgBox "No empty cells found in " & rngSrch.Address
        End If
    End If

    cBarcode.ClearContents   'clear the scanned code
End Sub

Excel VBA错误代码91：在代码运行且未进行任何编辑之后，“对象变量或未设置块变量”

2条答案

相关问题

热门标签

最新问答