Why is my excel macro working differently in another computer?

Question

I came up with a VBA to help my friend, but the Macro is doing different things on his computer. We tried every common solution on the net. We have the same tool references and excel versions so we really have no idea why it is working differently. Below is a summary of the problem and the code I devised.

Problem: The excel image shows a simple blueprint of a warehouse. In cell A20:C30, we want a Macro to be able to insert the pallet ID into its corresponding pallet location. E.g. Pallet ID 656816 would go to B16 while 656822 would go to C16. Please do ignore the rest on the excel sheet. Excel Image

Code:`

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

It is working exactly what I wanted on my machine, but when I sent the file over to my friend, it works differently. For instance, the Macro would prompt the error message "There is another pallet"(even though cell is blank) and pastes the pallet ID 656816 into Cell B4.

We tried tweaking the codes here and there but to no avail as well. We apologize in advance if it's because of some beginner mistakes!

Thank you for your time and assistance!

user_4552529 · Answer

I got the same error as you described due to the setup of the find function.

So what I changed in the Cells.Find() function was:

=xlPart -> =xlWhole 'To look at the whole cell
=xlFormulas2 -> xlFormulas 'It's the standard, would think the previous could be a defined variable...

You could also elaborate to change the:

MatchCase:=False -> MatchCase:=True 'If Search is Case sensitive

I also added ThisWorkbook.Worksheets("VNA"). to make sure the formula checks the correct sheet/have the right reference.

Revised the code to:

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

Result:

Why is my excel macro working differently in another computer?

1 Answers