excel - 有没有可以修复我当前代码的代码？

Question

我创建了一个签入/签出系统。我的代码仅适用于一张纸时效果很好。但是当我尝试将此代码应用于多个工作表时，出现错误，我尝试了三个代码，但仍然无法让 excel 将代码应用于每个单独的工作表。我使用的任何代码都会混淆 excel 并将我重定向到第一张表。

我使用的第一个代码

dim WS as worksheet
for each WS in sheets
(my code)
next WS

我使用的第二个代码

For Each Worksheet In ThisWorkbook.worksheets
sheets("may","June","july").activate
(my code)
next worksheet

我也试过了，（worksheet.activate）但是这段代码将我重定向到第一张表并丢失其他表上的数据

这是我正在尝试开始工作的当前代码

Sub Check_in()
For Each Worksheet In ThisWorkbook.worksheets
worksheets("April", "May", "June").Activate
Dim code As String: code = InputBox("Please scan a barcode", "Scan    procedure")
If code = "" Then MsgBox ("No code scanned"): Exit Sub
Dim NbChIn As Integer: NbChIn = application.CountIf(Range("STORE_RECORDS [CHECKED OUT on its way to analytical lab]"), code)
Dim NbChOut As Integer: NbChOut = application.CountIf(Range("STORE_RECORDS[SAMPLE RECEIVED from analytical lab]"), code)


If NbChIn > NbChOut And NbChIn > 0 Then
MsgBox ("This sample is already Checked-out" & Chr(10) & "Please click  sample received and retry"): Exit Sub
Else
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = code
Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = Now
End If
Next Worksheet
End Sub

Sub Check_Out()
Dim code As String: code = InputBox("Please scan a barcode", "Scan procedure")
If code = "" Then MsgBox ("No code scanned"): Exit Sub
Dim NbChIn As Integer: NbChIn = application.CountIf(Range("STORE_RECORDS[CHECKED OUT on its way to analytical lab]"), code)
Dim NbChOut As Integer: NbChOut = application.CountIf(Range("STORE_RECORDS[SAMPLE RECEIVED from analytical lab]"), code)
If NbChIn = NbChOut And NbChIn > 0 Then
    MsgBox ("This sample has already been received" & Chr(10) & "Please check it out and retry"): Exit Sub
Else
    If Range("STORE_RECORDS[CHECKED OUT on its way to analytical lab]").Find(code, , , xlWhole, , xlPrevious) Is Nothing Then MsgBox ("No match, ask Carlos !"): Exit Sub
    Range("STORE_RECORDS[CHECKED OUT on its way to analytical lab]").Find(code, , , xlWhole, , xlPrevious).Offset(0, 2) = code
    Range("STORE_RECORDS[CHECKED OUT on its way to analytical lab]").Find(code, , , xlWhole, , xlPrevious).Offset(0, 3) = Now
End If
End Sub'

代码通常会显示错误数量的参数，或者对象不支持此属性或方法。代码仅适用于工作表 1 或在我的情况下（四月）我希望此代码适用于按月组织的每张工作表。

标签： excelcheckoutcheckin