writing from userform to sheets - overwriting rows above

[ajm]

I have set up a userform to act as a cancellation form formy users as part of a trip booking model. Each trip has its own sheet (sheetname is the trip name) upon which a user can enter people’s details to attend.If the person changes their mind, I have set up a Cancellation form for theusers to process this also. This consists of a combo box where the user selectsthe name of the person cancelling their trip, and a listbox which shows thetrips currently booked for the particular person. The user simply selects theperson’s name from the combobox, and then selects the trip they wish to canceland the macro looks for that sheet, finds the users name in the list wishing toattend, then adds “Strike out CANCELLED” to the same row but in Col A, and thetime stamp, user’s name, and a cancellation reason to the same row but in columnsJ, K, L.


Well, that’s how it should be happening. Strangely, themacro is putting the “Strike out CANCELLED” exactly where it should but iswriting the time stamp, user’s name, and cancellation reason to all rows thathave been used for that booking.

Date of Change	Team Member's Name	Cancel Reason
11/01/2019 10:41	john	he wants pumpkin
11/01/2019 10:41	john	he wants pumpkin
11/01/2019 10:41	john	he wants pumpkin
11/01/2019 10:41	john	he wants pumpkin

<tbody> </tbody>

This is for a member who appears on the last line, not all 4lines.

What have I done incorrectly?

Sub CancelTrip()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
Dim ws As Worksheet
Dim lItem As Long
Dim wsTripsRg As Range, wsTripsRg2 As Range, rngFoundcancel As Range
Dim ReturnTripSht As Variant, defCancel As Variant, strCancelReason As Variant
Dim strSearchName As String
    With Application
        .ScreenUpdating = False
    End With
    
CancelQs:
strCancelReason = Application.InputBox("Trip Cancellation?", "Why is the member cancelling their trip?")
If strCancelReason = False Then
defCancel = MsgBox("This trip will NOT be cancelled." & vbNewLine & _
vbNewLine & "Are you sure this is what you want?", vbCritical + vbYesNo)
    If defCancel = vbYes Then
    Exit Sub
    
    ElseIf defCancel = vbNo Then
    GoTo CancelQs
    End If
ElseIf strCancelReason = "" Then
MsgBox "You must include a reason if a member cancels a trip", vbCritical, "Warning"
GoTo CancelQs
End If


strSearchName = frmBookings.cboMembName.Value
    For lItem = 0 To frmBookings.LbxExistBook.ListCount - 1
        If frmBookings.LbxExistBook.Selected(lItem) = True Then
        
        Set wsTripsRg = Worksheets("Trips").Range("TripDate")
        Set wsTripsRg2 = Worksheets("Trips").Range("TripSheetName")
     'MsgBox (frmBookings.LbxExistBook.List(lItem))
    ReturnTripSht = Application.Index(wsTripsRg2, Application.Match(frmBookings.LbxExistBook.List(lItem), wsTripsRg, 0))
            
          With Sheets(ReturnTripSht)
            
       
        '.Select
        
        'Set the first and last row to loop through
        Firstrow = 5
        Lastrow = .Cells(.Rows.Count, "c").End(xlUp).Row
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the A column in this example
            With .Cells(Lrow, "c")

                If Not IsError(.Value) Then
                    If .Value = strSearchName Then .Offset(0, -2).Value = "Strike out CANCELLED"  'this works
                    .Offset(0, 10).Value = frmBookings.txtLogDate.Text   'this writes to the correct row and all rows above
                    .Offset(0, 11).Value = frmBookings.txtTmMember.Text   'this writes to the correct row and all rows above
                    .Offset(0, 12).Value = strCancelReason       'this writes to the correct row and all rows above
                                        
                End If

            End With
        Next Lrow
        
        End With
 End If
   Next lItem
   
   
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

 

[Paul Ked]

                If Not IsError(.Value) Then
                    If .Value = strSearchName Then
                        .Offset(0, -2).Value = "Strike out CANCELLED"  'this works
                        .Offset(0, 10).Value = frmBookings.txtLogDate.Text   'this writes to the correct row and all rows above
                        .Offset(0, 11).Value = frmBookings.txtTmMember.Text   'this writes to the correct row and all rows above
                        .Offset(0, 12).Value = strCancelReason       'this writes to the correct row and all rows above
                    End If
                End If

 

[ajm]

                If Not IsError(.Value) Then
                    If .Value = strSearchName Then
                        .Offset(0, -2).Value = "Strike out CANCELLED"  'this works
                        .Offset(0, 10).Value = frmBookings.txtLogDate.Text   'this writes to the correct row and all rows above
                        .Offset(0, 11).Value = frmBookings.txtTmMember.Text   'this writes to the correct row and all rows above
                        .Offset(0, 12).Value = strCancelReason       'this writes to the correct row and all rows above
                    End If
                End If

something so simple! many thanks.

 

[Paul Ked]

So simple