Move row last on change of value
 

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

Move row last on change of value
 

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 wrote:

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

Move row last on change of value
 

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

Dave Peterson wrote:
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 wrote:

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