Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
move offsetting values
Have data that looks like this
100- 100 100- 98- 98 97- 97- 97 Data will be sorted in descending absolute value order. If 2 lines offset, need to move lines to another worksheet. If all matching lines on value do not offset to zero, leave alone. THANKS for any help... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
move offsetting values
My first recommendation is that rather than move things to a different
worksheet, you first make a copy of the sheet, and then make a macro for each sheet, one that deletes all items that net to zero, and the other to delete all items that don't net to zero. Result will be the same, but easier to code in my opinion. I will try to come up with code for the macros and send it in a separate reply if I have time. Keith "Don S" wrote: Have data that looks like this 100- 100 100- 98- 98 97- 97- 97 Data will be sorted in descending absolute value order. If 2 lines offset, need to move lines to another worksheet. If all matching lines on value do not offset to zero, leave alone. THANKS for any help... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
move offsetting values
Here's a macro to remove the ones that net to zero (assuming you are taking
my advice to copy the sheet first and run two macros, one to remove net zeros, and the other to remove those that don't net to zero). Sub RemoveNetZeros() Dim MyFirstRow, MyLastRow As Long Dim MyValue As Double Do While ActiveCell.Value < "" Do Until Abs(ActiveCell.Value) < Abs(ActiveCell.Offset(1, 0).Value) If Abs(ActiveCell.Value) = Abs(ActiveCell.Offset(1, 0).Value) And _ Abs(ActiveCell.Value) < Abs(ActiveCell.Offset(-1, 0).Value) Then MyFirstRow = ActiveCell.Row End If MyValue = MyValue + ActiveCell.Value ActiveCell.Offset(1, 0).Select Loop MyLastRow = ActiveCell.Row MyValue = MyValue + ActiveCell.Value If MyValue = 0 Then Range(Cells(MyFirstRow, ActiveCell.Column), Cells(MyLastRow, ActiveCell.Column)).Select Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If MyValue = 0 Loop End Sub You start this macro with the activecell being the first value at the top (row 2 if you have labels in row 1). As always, test on sample data first to make sure desired results are obtained. I tested and it worked for me, regardless of the data in the first few rows, which is where the macro would have been most likely to fail. For example, I tested it on the first absolute value being in row one only, in rows one and two only, and in rows one through three only. Worked on all for me. Hope this helps. I'll try to send the macro to remove those that don't net to zero if time allows. Keith "Don S" wrote: Have data that looks like this 100- 100 100- 98- 98 97- 97- 97 Data will be sorted in descending absolute value order. If 2 lines offset, need to move lines to another worksheet. If all matching lines on value do not offset to zero, leave alone. THANKS for any help... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
move offsetting values
Instead of Entirerow .delete , could you cut and paste to diifferent
worksheet at that point? "Keithlo" wrote: Here's a macro to remove the ones that net to zero (assuming you are taking my advice to copy the sheet first and run two macros, one to remove net zeros, and the other to remove those that don't net to zero). Sub RemoveNetZeros() Dim MyFirstRow, MyLastRow As Long Dim MyValue As Double Do While ActiveCell.Value < "" Do Until Abs(ActiveCell.Value) < Abs(ActiveCell.Offset(1, 0).Value) If Abs(ActiveCell.Value) = Abs(ActiveCell.Offset(1, 0).Value) And _ Abs(ActiveCell.Value) < Abs(ActiveCell.Offset(-1, 0).Value) Then MyFirstRow = ActiveCell.Row End If MyValue = MyValue + ActiveCell.Value ActiveCell.Offset(1, 0).Select Loop MyLastRow = ActiveCell.Row MyValue = MyValue + ActiveCell.Value If MyValue = 0 Then Range(Cells(MyFirstRow, ActiveCell.Column), Cells(MyLastRow, ActiveCell.Column)).Select Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If MyValue = 0 Loop End Sub You start this macro with the activecell being the first value at the top (row 2 if you have labels in row 1). As always, test on sample data first to make sure desired results are obtained. I tested and it worked for me, regardless of the data in the first few rows, which is where the macro would have been most likely to fail. For example, I tested it on the first absolute value being in row one only, in rows one and two only, and in rows one through three only. Worked on all for me. Hope this helps. I'll try to send the macro to remove those that don't net to zero if time allows. Keith "Don S" wrote: Have data that looks like this 100- 100 100- 98- 98 97- 97- 97 Data will be sorted in descending absolute value order. If 2 lines offset, need to move lines to another worksheet. If all matching lines on value do not offset to zero, leave alone. THANKS for any help... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
move offsetting values
Sure. You just need to change the part of the code that deletes rows to
instead have it cut, select the new sheet, paste at the correct location, then switch back to the original sheet and continue. As I said in my original reply, that method will require more code and complexity in my opinion, which is why I recommend this other way. Also, this way achieves the same result. You can take this code and modify it, since the basic logic is there. You can use the macro recorder to get the code for cut and paste. Keith "Don S" wrote: Instead of Entirerow .delete , could you cut and paste to diifferent worksheet at that point? "Keithlo" wrote: Here's a macro to remove the ones that net to zero (assuming you are taking my advice to copy the sheet first and run two macros, one to remove net zeros, and the other to remove those that don't net to zero). Sub RemoveNetZeros() Dim MyFirstRow, MyLastRow As Long Dim MyValue As Double Do While ActiveCell.Value < "" Do Until Abs(ActiveCell.Value) < Abs(ActiveCell.Offset(1, 0).Value) If Abs(ActiveCell.Value) = Abs(ActiveCell.Offset(1, 0).Value) And _ Abs(ActiveCell.Value) < Abs(ActiveCell.Offset(-1, 0).Value) Then MyFirstRow = ActiveCell.Row End If MyValue = MyValue + ActiveCell.Value ActiveCell.Offset(1, 0).Select Loop MyLastRow = ActiveCell.Row MyValue = MyValue + ActiveCell.Value If MyValue = 0 Then Range(Cells(MyFirstRow, ActiveCell.Column), Cells(MyLastRow, ActiveCell.Column)).Select Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If MyValue = 0 Loop End Sub You start this macro with the activecell being the first value at the top (row 2 if you have labels in row 1). As always, test on sample data first to make sure desired results are obtained. I tested and it worked for me, regardless of the data in the first few rows, which is where the macro would have been most likely to fail. For example, I tested it on the first absolute value being in row one only, in rows one and two only, and in rows one through three only. Worked on all for me. Hope this helps. I'll try to send the macro to remove those that don't net to zero if time allows. Keith "Don S" wrote: Have data that looks like this 100- 100 100- 98- 98 97- 97- 97 Data will be sorted in descending absolute value order. If 2 lines offset, need to move lines to another worksheet. If all matching lines on value do not offset to zero, leave alone. THANKS for any help... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
move offsetting values
THANKS for all your help.......
"Keithlo" wrote: Sure. You just need to change the part of the code that deletes rows to instead have it cut, select the new sheet, paste at the correct location, then switch back to the original sheet and continue. As I said in my original reply, that method will require more code and complexity in my opinion, which is why I recommend this other way. Also, this way achieves the same result. You can take this code and modify it, since the basic logic is there. You can use the macro recorder to get the code for cut and paste. Keith "Don S" wrote: Instead of Entirerow .delete , could you cut and paste to diifferent worksheet at that point? "Keithlo" wrote: Here's a macro to remove the ones that net to zero (assuming you are taking my advice to copy the sheet first and run two macros, one to remove net zeros, and the other to remove those that don't net to zero). Sub RemoveNetZeros() Dim MyFirstRow, MyLastRow As Long Dim MyValue As Double Do While ActiveCell.Value < "" Do Until Abs(ActiveCell.Value) < Abs(ActiveCell.Offset(1, 0).Value) If Abs(ActiveCell.Value) = Abs(ActiveCell.Offset(1, 0).Value) And _ Abs(ActiveCell.Value) < Abs(ActiveCell.Offset(-1, 0).Value) Then MyFirstRow = ActiveCell.Row End If MyValue = MyValue + ActiveCell.Value ActiveCell.Offset(1, 0).Select Loop MyLastRow = ActiveCell.Row MyValue = MyValue + ActiveCell.Value If MyValue = 0 Then Range(Cells(MyFirstRow, ActiveCell.Column), Cells(MyLastRow, ActiveCell.Column)).Select Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If MyValue = 0 Loop End Sub You start this macro with the activecell being the first value at the top (row 2 if you have labels in row 1). As always, test on sample data first to make sure desired results are obtained. I tested and it worked for me, regardless of the data in the first few rows, which is where the macro would have been most likely to fail. For example, I tested it on the first absolute value being in row one only, in rows one and two only, and in rows one through three only. Worked on all for me. Hope this helps. I'll try to send the macro to remove those that don't net to zero if time allows. Keith "Don S" wrote: Have data that looks like this 100- 100 100- 98- 98 97- 97- 97 Data will be sorted in descending absolute value order. If 2 lines offset, need to move lines to another worksheet. If all matching lines on value do not offset to zero, leave alone. THANKS for any help... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offsetting | Excel Discussion (Misc queries) | |||
Offsetting and HLOOKUP | Excel Worksheet Functions | |||
offsetting values of multiple cells! | Excel Worksheet Functions | |||
Offsetting Cells | Excel Programming | |||
Offsetting a varible?? | Excel Programming |