Code failing to place item at required location

[kelly mort]

Variables

Private fEnterValue As Variant
Private fNameID As Variant
Private fChangeValue As Variant
Private fSName As Variant

Textbox enter event

Private Sub freg2_Enter()
If freg3 <> "" Then fEnterValue = Trim(freg2.Text)
Select Case freg3.Text
    Case "X", "Y", "Z"
    For Each fSName In Sheet1.[AZ7:AZ407]
        If fSName = fEnterValue Then
            fNameID = fSName.Offset(0, -1)
            Exit For
        End If
    Next fSName
    
    Case Else
    For Each fSName In Sheet1.[C7:C407]
        If fSName = fEnterValue Then
            fNameID = fSName.Offset(0, -1)
            Exit For
        End If
    Next fSName
End Select
End Sub

Textbox change event

Private Sub freg2_Change()
    freg2 = UCase(freg2)
    If freg3 <> "" Then fChangeValue = Trim(freg2.Text)
End Sub

Command button click event

Private CmdCompare_Click()
    If Trim(fEnterValue) <> Trim(fChangeValue) Then
    Select Case freg3.Text
        Case "X", "Y", "Z"
            For Each fNameID In Sheet1 .[AY7:AY407]
                If fNameID.Offset(0, 1) = fEnterValue Then
                    fNameID.Offset(0, 1) = fChangeValue
                Exit For
                End If
            Next fNameID
    
        Case Else
            For Each fNameID In  Sheet1.[B7:B407]
                If fNameID.Offset(0, 1) = fEnterValue Then
                    fNameID.Offset(0, 1) = fChangeValue
                Exit For
                End If
            Next fNameID
    End Select
    End If
End Sub

This code is placing the changed name under the last item in the database instead of placing it in the place or location of where the item (before changed) is located actually.


For example, say I have “Blue Cats” and I change it to “Blue cars”, it places the “Blue Cars” under the last item in the column while having the “Blue cats” at its original place. Meanwhile I want to have the “Blue Cats” replaced with the “Blue Cars”. I have tried all the tricks I know so far and it’s freaking me out. Can someone pull me out?.