Move row last on change of value

[Jan Kronsell]

I have the following code

Public val As String


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And UCase(Target.Value) = "JA" And UCase(val) =
"NEJ" Then
Target.EntireRow.Cut
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
val = Target.Value
End Sub


If I change the value from NO to YES in a row in column C, I like the row ro
be moved down below the other used rows in the sheet. It moves the row
allright, but then fails with

Runtime Error 13: Type Mismatch in line

If Target.Column = 3 And UCase(Target.Value) = "YES" And UCase(val) =
"NO" Then

If I disable events nothing happens at all.

How can I Solve this?

Jan

 

[Dave Peterson]

I don't quite get what you're doing with val, but if you're changing the value
in a cell, then you want to check target.value to see if it's yes.

I'd use something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim DestCell As Range

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, Me.Range("C:C")) Is Nothing Then
Exit Sub
End If

If UCase(Target.Value) = UCase("yes") Then
With Me
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Application.EnableEvents = False
Target.EntireRow.Cut _
Destination:=DestCell
Application.EnableEvents = True

End If
End Sub

The application.enableevents = false stops the next change from causing the
worksheet_change to fire again.

 

[Jan Kronsell]

Hi Dave

Thank you. I try your solution a little later, and get back to you.

I use val to register the present value of the target. The value can be any
of a number of values, and tne move should only be carried out if the value
changes from explicit No to Yes. Change from any other value than No to Yes
should leave the row, where it is.

Jan