Hello ufopilot,
You have made a common mistake when using the Find method for a Range. Because the Find method used for a Range object is the same structure as the Windows Find/Replace dialog, many of the values are retained after they have been used. This is one instance where VBA does not clear all the variables after the Sub or Function ends. Get into the habit of setting these variables when you use the Find method and you will eliminate a lot of problems.
From the VBA Help Files
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.
Corrected Code is in Blue
Sub zzShiftChange()
'history preserve
ActiveSheet.Unprotect
Dim myDetailFind As Integer
Dim rngDetail As Range
Dim rngDetailToSearch As Range
Dim rngDetailFound As Range
Set wks = ActiveSheet
Set rngDetailToSearch = ActiveSheet.Range("K88:K89")
Set rngDetailFound = rngDetailToSearch.Find(What:=wks.Range("M85"), _
LookIn:=xlValues, LookAt:=whole ,MatchCase:=False)
If Not rngDetailFound Is Nothing Then
ActiveSheet.Range("L91:BM91").Copy
rngDetailFound.Offset(0, 1).PasteSpecial xlValues
MsgBox myFind & " History from last shift has been logged. Please change your shift and restart the timer"
Else
Range("B24").Select
End If
ActiveSheet.Protect
End Sub
Bookmarks