Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cntl Key move/select to change in value | Excel Discussion (Misc queries) | |||
Formula to change move down one cell | Excel Discussion (Misc queries) | |||
Worksheet change: move to next cell | Excel Programming | |||
How 2 tie cells in same row so they move as a ROW if table change | Excel Discussion (Misc queries) | |||
when i move or copy a sheet, dates change by one day?? | Excel Discussion (Misc queries) |