Worksheet_Change

[JOEE1979]

Im not sure where I screwed up, I need help please

Private Sub Worksheet_Change(ByVal Target As Range)

Dim f As Range
Dim resp As VbMsgBoxResult
Dim i As Long


For Each f In Range("F475:F554")
    If Cells(f.Row, 6).Value = "Completed" Then
        If Cells(f.Row, 5).Value = "Running Repair" Then
     resp = MsgBox("Is unit returned to service?", _
        vbYesNo + vbQuestion)
      If resp = vbYes Then
        Set f = Range("I:J").Find("RETURNED TO SERVICE", , xlValues, xlPart, , , False)
        If Not f Is Nothing Then
          i = f.Row + 2
          Set f = Range("I:J").Find(Range(f.Row, 1).Value, , xlValues, xlWhole, , , False)
          If Not f Is Nothing Then
            MsgBox "This unit already exists in the section."
            Exit Sub
          End If
          Do While True
            If Range("I" & i).Value = "" Then
              Range("I" & i).Value = Range(f.Row, 1).Value
              Exit Do
            End If
            i = i + 1
          Loop
        End If
      End If
    End If
  End If
Next
End Sub

 

[Peter_SSs]

Im not sure where I screwed up, I need help please

Then it would help if you explained

• what the code is supposed to do
• in what way it is not doing that (error messages, wrong actions etc)
• some details of what your data is like and how it is laid out. Consider XL2BB

 

[JOEE1979]

If "Running Repair" shows up in column E & "Completed" in column F, then I wanted a MsgBox to ask a question. If yes, then the number in column A would appear in Range (I:J).

Shift Update - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
474	Unit #	Assigned to	W/O #	Activity	W/O Type	Status
475	1235	Joe	56456465	Boost	Running Repair	Completed
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493								MTO Stickered	RETURNED TO SERVICE Repairs		RETURNED TO SERVICE Sign In's		Waiting for Paperwork
494
495									1235
496
497
498
Shift Update

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K466:L491	Expression	=MOD(ROW(),2)=0	text	NO
D476:D554	Expression	=MOD(ROW(),2)=0	text	NO
K495:L511	Expression	=MOD(ROW(),2)=0	text	NO
H487:J491,M487:M491	Expression	=MOD(ROW(),2)=0	text	NO
A475:F475,A476:C486,E476:F486	Expression	=MOD(ROW(),2)=0	text	NO
M495:M502	Expression	=MOD(ROW(),2)=0	text	NO
I495:J511	Expression	=MOD(ROW(),2)=0	text	NO
H495:H502	Expression	=MOD(ROW(),2)=0	text	NO
H468,J468,H469:J486,M470:M486	Expression	=MOD(ROW(),2)=0	text	NO
A487:C506,E487:F506	Expression	=MOD(ROW(),2)=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B475:B498	List	=Employee
M474:M491	List	Yes,No
D475:D554	List	=Activity
E475:E554	List	=WO_Type
F475:F554	List	=Status

 

[Peter_SSs]

As best as I can make out what you might want ..
Test with a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, f As Range
  Dim resp As VbMsgBoxResult
  Dim lr As Long
  
  Set Changed = Intersect(Target, Range("F475:F554"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If c.Value = "Completed" Then
        If c.Offset(, -1).Value = "Running Repair" Then
          resp = MsgBox("Is unit returned to service?", vbYesNo + vbQuestion)
          If resp = vbYes Then
            lr = Range("I:J").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If lr > 492 Then
              If lr = 493 Then lr = 494
              Set f = Range("I:J").Find(Range("A" & c.Row).Value, , xlValues, xlWhole, , , False)
              If f Is Nothing Then
                Range("I" & lr + 1).Value = Range("A" & c.Row).Value
              Else
                MsgBox "This unit already exists in the section."
              End If
            End If
          End If
        End If
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[JOEE1979]

For some reason its putting the number from ("A" & c.Row") into cell("I 540")
I need it to start in cell (I495)

 

[Peter_SSs]

Sounds like there might be data further down the worksheet not shown in the post 3 mini sheet.
Try this version. (It also allows for multiple cells in column F being changed at once.)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, f As Range
  Dim resp As VbMsgBoxResult
  Dim nr As Long
  Dim UnitNum As String
  
  Set Changed = Intersect(Target, Range("F475:F554"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If c.Value = "Completed" Then
        If c.Offset(, -1).Value = "Running Repair" Then
          UnitNum = Range("A" & c.Row).Value
          resp = MsgBox("Is unit " & UnitNum & " returned to service?", vbYesNo + vbQuestion)
          If resp = vbYes Then
            If IsEmpty(Range("I495").Value) Then
              nr = 495
            ElseIf IsEmpty(Range("I496").Value) Then
              nr = 496
            Else
              nr = Range("I495").End(xlDown).Row + 1
            End If
            Set f = Range("I:J").Find(UnitNum, , xlValues, xlWhole, , , False)
            If f Is Nothing Then
              Range("I" & nr).Value = UnitNum
            Else
              MsgBox "Unit " & UnitNum & " already exists in the section."
            End If
          End If
        End If
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub