For each loop getting tricky

[kelly mort]

I am using this code to capture two events “Enter” and “Change” textbox data. So when there is a change in both variables, I want to use the “Enter” variable to locate the ID of that item. The reason being that I will change that item with the “Change” variable. I observed I can’t change it since the location is same. So I tried using some of the tricks I know so far but the VBE is just laughing at me.


What is it that I am doing wrongly?


Thanks

Option Explicit
Dim EnterValue As Variant
Dim NameID As Variant
Dim ChangeValue As Variant
Dim SName As Variant


Private Sub Rw2_Change()
    Rw2 = UCase(Rw2)
    ChangeValue = Rw2.Text
End Sub
Private Sub Rw2_Enter()
    EnterValue = Rw2.Text
End Sub
Private Sub CmdCompareNameChange_Click()


With Sheet1
    If Trim(EnterValue) = Trim(ChangeValue) Then
        MsgBox "Match " & EnterValue & " , " & ChangeValue
    Else
        For Each SName In .[C4:C404]
            For Each NameID In .[B4:B404]
                If StName = EnterValue Then
                    NameID = SName.Offset(0, -1)
                    'NameID.Offset(0, 1) = ChangeValue
                    NameID.Offset(0, 1).Interior.ColorIndex = 37
                    MsgBox "Not Match " & EnterValue & " , " & ChangeValue & " , " '& NameID.Offset(0, 1).Value
                    Exit For
                End If
            Next NameID
        Next SName
    End If
End With
End Sub

 

[kelly mort]

Option Explicit
Dim EnterValue As Variant
Dim NameID As Variant
Dim ChangeValue As Variant
Dim SName As Variant


Private Sub Rw2_Change()
    Rw2 = UCase(Rw2)
    ChangeValue = Rw2.Text
End Sub


Private Sub Rw2_Enter()
EnterValue = Trim(Rw2.Text)
With Sheet1
    For Each SName In .[C4:C404]
        If SName = EnterValue Then
            NameID = SName.Offset(0, -1)
            Exit For
        End If
    Next SName
End With
End Sub


Private Sub CmdCompareNameChange_Click()
With Sheet1
    If Trim(EnterValue) = Trim(ChangeValue) Then
    
    Else
    
        For Each NameID In .[B4:B404]
        If NameID.Offset(0, 1) = EnterValue Then
            NameID.Offset(0, 1) = ChangeValue
            MsgBox "Not Match " & EnterValue & " , " & ChangeValue & " , " & NameID
            Exit For
        End If
        Next NameID
        
    End If
End With
End Sub

I just fixed it my way.

If there is a better way, I am willing to know.

Thanks

 

[Fluff]

What is StName?

 

[kelly mort]

What is StName?

That was a typo.

Same as the SName

 

[Fluff]

Ok, I see that you've got it working

 

[kelly mort]

Ok, I see that you've got it working

I am making a gradual progress I guess.

 

[Fluff]

You're certainly making progress & we've all been in that position.