excel - 为什么我的 excel 宏在另一台计算机上的工作方式不同？

Question

我想出了一个 VBA 来帮助我的 friend ，但是宏在他的计算机上做不同的事情。我们尝试了网络上所有常见的解决方案。我们有相同的工具引用和 Excel 版本，所以我们真的不知道为什么它的工作方式不同。以下是问题的摘要和我设计的代码。

问题:
excel 图像显示了一个仓库的简单蓝图。在单元格 A20:C30 中，我们希望宏能够将托盘 ID 插入其相应的托盘位置。例如。托盘 ID 656816 将转到 B16，而 656822 将转到 C16。请忽略excel表上的其余部分。
Excel Image

代码:`

Sub PalletIn()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myFind As String
    Dim myReplace As String

    myLastRow = ThisWorkbook.Worksheets("VNA").Cells(Rows.Count, "C").End(xlUp).Row
    Application.ScreenUpdating = False

    'start loop
    For myRow = 21 To myLastRow
        'Find and replace values
        myFind = ThisWorkbook.Worksheets("VNA").Cells(myRow, "C")
        myReplace = ThisWorkbook.Worksheets("VNA").Cells(myRow, "B")
        'Fix the search range
        Range("B4:P17").Select
        'Ignore errors that result from finding no matches
        On Error Resume Next
        'Do all replacements on sheet        
        Cells.Find(What:=myFind, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Offset(-1, 0).Select
        If Not IsEmpty(ActiveCell.Value) Then MsgBox "There is another pallet in this location!"
        If Not IsEmpty(ActiveCell.Value) Then Exit Sub
        Cells(myRow, "B").Copy _
        Destination:=ActiveCell
        'Reset error checking
        On Error GoTo 0
    Next myRow

    Application.ScreenUpdating = True

    MsgBox "Pallet in!"

End Sub

它在我的机器上完全符合我的要求，但是当我将文件发送给我的 friend 时，它的工作方式有所不同。例如，宏会提示错误消息“还有另一个托盘”(即使单元格为空白)并将托盘 ID 656816 粘贴到单元格 B4 中。

我们尝试在这里和那里调整代码，但也无济于事。如果是因为一些初学者的错误，我们提前道歉!

感谢您的时间和帮助!

Answer 1

由于查找功能的设置，我遇到了与您描述的相同的错误。

所以我在 Cells.Find() 中所做的更改功能是:

=xlPart -> =xlWhole 'To look at the whole cell

=xlFormulas2 -> xlFormulas 'It's the standard, would think the previous could be a defined variable...

您还可以详细说明更改:

MatchCase:=False -> MatchCase:=True '如果搜索区分大小写

我还添加了 ThisWorkbook.Worksheets("VNA").确保公式检查正确的工作表/具有正确的引用。

将代码修改为:

Sub PalletIn()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myFind As String
    Dim myReplace As String

    myLastRow = ThisWorkbook.Worksheets("VNA").Cells(Rows.Count, "C").End(xlUp).Row
    Application.ScreenUpdating= False

    'start loop
    For myRow = 21 To myLastRow
        'Find and replace values
        myFind = ThisWorkbook.Worksheets("VNA").Cells(myRow, "C")
        myReplace = ThisWorkbook.Worksheets("VNA").Cells(myRow, "B")
        'Fix the search range
        Range("B4:P17").Select
        'Ignore errors that result from finding no matches
        On Error Resume Next
        'Do all replacements on sheet
        ThisWorkbook.Worksheets("VNA").Cells.Find(What:=myFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Offset(-1, 0).Select

        If Not IsEmpty(ActiveCell.Value) Then 'Instead of having two "IF" function you could have one, which is faster and easier to read.
            MsgBox "There is another pallet in this location!" & vbCrLf & "(Pallet Location: " & ActiveCell.Offset(-1, 0).Value & ", Pallet ID: " & ActiveCell.Value & ")"
            Exit Sub
        End If

        ActiveCell = ThisWorkbook.Worksheets("VNA").Cells(myRow, "B") 'This is faster, but the way your wrote is more preferable if you want to keep cell formatting.
        'Reset error checking
        On Error GoTo 0
    Next myRow

    Application.ScreenUpdating = True

    MsgBox "Pallet in!"

End Sub

结果: