Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
I have an amortization table where the numbers eventually turn negatve. How
can I find the negative numbers and delete all rows where a negative number appears? Thanks for your anticipated response. -- Burt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
Do you want to delete the row if any cell in the row is negative or only if
all the cells in the row are negative? If the former, is there one particular column that goes negative before any of the others do (if so, that will make for more efficient code)? Rick "BurtArkin" wrote in message ... I have an amortization table where the numbers eventually turn negatve. How can I find the negative numbers and delete all rows where a negative number appears? Thanks for your anticipated response. -- Burt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
The former is correct, and the first negative number appears in column I
(eye). Also, when I try to call a second macro, the directions somehow "lose" their way, and fill in data in columns to the right of the regular data. How can I regulate that? The reason is I want the macros to run in sequence, transparent to the user. Thanks again. -- Burt "Rick Rothstein (MVP - VB)" wrote: Do you want to delete the row if any cell in the row is negative or only if all the cells in the row are negative? If the former, is there one particular column that goes negative before any of the others do (if so, that will make for more efficient code)? Rick "BurtArkin" wrote in message ... I have an amortization table where the numbers eventually turn negatve. How can I find the negative numbers and delete all rows where a negative number appears? Thanks for your anticipated response. -- Burt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
I think this will remove the rows you want to remove (test on a copy of your
data first to be sure)... Sub RemoveNegativeRows() Dim X As Long Dim LastCell As Long With Worksheets("Sheet2") LastCell = .Cells(Rows.Count, "I").End(xlUp).Row For X = LastCell To 1 Step -1 If .Cells(X, "I").Value = 0 Then Exit For .Cells(X, "I").EntireRow.Delete Next End With End Sub I'm not exactly sure what you mean by the new question you have added about the second macro. Perhaps if you posted some code for this second macro, and provide an expanded description of what it is supposed to do and what it actually is doing, then maybe someone here can suggest something to you. Rick "BurtArkin" wrote in message ... The former is correct, and the first negative number appears in column I (eye). Also, when I try to call a second macro, the directions somehow "lose" their way, and fill in data in columns to the right of the regular data. How can I regulate that? The reason is I want the macros to run in sequence, transparent to the user. Thanks again. -- Burt "Rick Rothstein (MVP - VB)" wrote: Do you want to delete the row if any cell in the row is negative or only if all the cells in the row are negative? If the former, is there one particular column that goes negative before any of the others do (if so, that will make for more efficient code)? Rick "BurtArkin" wrote in message ... I have an amortization table where the numbers eventually turn negatve. How can I find the negative numbers and delete all rows where a negative number appears? Thanks for your anticipated response. -- Burt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
The code worked perfectly! Thank you. The other issue is this--I want the
spreadsheet to import data, which is what "macro1" does. Then I run "macro2", and then the one you supplied. What I would like is to combine all the macros into one, or try to "call" the second and then the third. When I place your addition into "macro2" nothing happens. Am I missing a step, or is there a way to stop/start? Appreciate your help. By the way, if all this could be done while the user remains in Access, and just imports the results of the spreadsheet, that would be really perfect! Thanks -- Burt "Rick Rothstein (MVP - VB)" wrote: I think this will remove the rows you want to remove (test on a copy of your data first to be sure)... Sub RemoveNegativeRows() Dim X As Long Dim LastCell As Long With Worksheets("Sheet2") LastCell = .Cells(Rows.Count, "I").End(xlUp).Row For X = LastCell To 1 Step -1 If .Cells(X, "I").Value = 0 Then Exit For .Cells(X, "I").EntireRow.Delete Next End With End Sub I'm not exactly sure what you mean by the new question you have added about the second macro. Perhaps if you posted some code for this second macro, and provide an expanded description of what it is supposed to do and what it actually is doing, then maybe someone here can suggest something to you. Rick "BurtArkin" wrote in message ... The former is correct, and the first negative number appears in column I (eye). Also, when I try to call a second macro, the directions somehow "lose" their way, and fill in data in columns to the right of the regular data. How can I regulate that? The reason is I want the macros to run in sequence, transparent to the user. Thanks again. -- Burt "Rick Rothstein (MVP - VB)" wrote: Do you want to delete the row if any cell in the row is negative or only if all the cells in the row are negative? If the former, is there one particular column that goes negative before any of the others do (if so, that will make for more efficient code)? Rick "BurtArkin" wrote in message ... I have an amortization table where the numbers eventually turn negatve. How can I find the negative numbers and delete all rows where a negative number appears? Thanks for your anticipated response. -- Burt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
It is kind of hard to say what might be wrong without seeing the code from
"macro2". It could be a timing problem or it could be a logic construction blocking the continuation of code or any number of other things. One thing you might try is constructing a fourth macro which does nothing be call each of the other macros one at a time (although this could still be subject to a timing problem). If this fourth macro idea does work, or is not an acceptable solution, you will have to post your "macro2" code so we can look at it. Rick The other issue is this--I want the spreadsheet to import data, which is what "macro1" does. Then I run "macro2", and then the one you supplied. What I would like is to combine all the macros into one, or try to "call" the second and then the third. When I place your addition into "macro2" nothing happens. Am I missing a step, or is there a way to stop/start? Appreciate your help. By the way, if all this could be done while the user remains in Access, and just imports the results of the spreadsheet, that would be really perfect! Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
Below is all the code for the three actions (yours included).
Sub Macro1() 'delete old data Range("A1:I33").Select 'Range("I33").Activate Range("a1").Activate Selection.ClearContents Selection.QueryTable.Delete 'enter new data With ActiveSheet.QueryTables.Add(Connection:=Array("OLE DB;Provider=Microsoft.Jet.OLEDB.4.0;Password=""""; User ID=Admin;Data Source=C:\dblink.mdb;Mode=Share Deny Write;Extended Properties=""", """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab", "ase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" _ , "jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co", "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("a1")) .CommandType = xlCmdTable .CommandText = Array("FASBTableTemp") .Name = "dblink_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceDataFile = "C:\dblink.mdb" .Refresh BackgroundQuery:=True End With Range("a1").Select Range("a1").Activate ' Macro2 this doesn't work properly End Sub Sub Macro2() 'add next year Range("A1").Select Range("f2").Select Range("f3").Select ActiveCell.FormulaR1C1 = "=+R[-1]C+1" Range("G3").Select ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]" Range("I2").Select ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]" Selection.AutoFill Destination:=Range("I2:I30"), Type:=xlFillDefault Range("I2:I30").Select Range("H2").Select Selection.AutoFill Destination:=Range("H2:H30"), Type:=xlFillDefault Range("H2:H30").Select Range("G3").Select ActiveCell.FormulaR1C1 = "=+R[-1]C[2]" Range("G3").Select Selection.AutoFill Destination:=Range("G3:G30"), Type:=xlFillDefault Range("G3:G30").Select Columns("G:j").Select Selection.Style = "Currency" Columns("G:j").EntireColumn.AutoFit Columns("G:j").Select Selection.Style = "Currency" Columns("G:j").EntireColumn.AutoFit 'fill in years automatically ActiveCell.Offset(2, -1).Range("A1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:A28"), Type:=xlFillDefault ActiveCell.Range("A1:A28").Select 'find first endyramt <0 & change recamt to correct number Set MYRANGE = Range("i2:i" & Cells(Rows.Count, "i").End(xlUp).Row) For Each c In MYRANGE If c.Value < "" And c.Value <= 0 Then c.Offset(0, -1).Value = c.Offset(0, -2).Value ' Do what you want Exit Sub End If Next 'RemoveNegativeRows this doesn't work properly End Sub Sub RemoveNegativeRows() Dim X As Long Dim LastCell As Long With Worksheets("FASBworksheet") LastCell = .Cells(Rows.Count, "I").End(xlUp).Row For X = LastCell To 1 Step -1 If .Cells(X, "I").Value = 0 Then Exit For .Cells(X, "I").EntireRow.Delete Next End With End Sub -- Burt "Rick Rothstein (MVP - VB)" wrote: It is kind of hard to say what might be wrong without seeing the code from "macro2". It could be a timing problem or it could be a logic construction blocking the continuation of code or any number of other things. One thing you might try is constructing a fourth macro which does nothing be call each of the other macros one at a time (although this could still be subject to a timing problem). If this fourth macro idea does work, or is not an acceptable solution, you will have to post your "macro2" code so we can look at it. Rick The other issue is this--I want the spreadsheet to import data, which is what "macro1" does. Then I run "macro2", and then the one you supplied. What I would like is to combine all the macros into one, or try to "call" the second and then the third. When I place your addition into "macro2" nothing happens. Am I missing a step, or is there a way to stop/start? Appreciate your help. By the way, if all this could be done while the user remains in Access, and just imports the results of the spreadsheet, that would be really perfect! Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
If those 3 macros work fine when executed individually, then you should be
able to "gang them together" by just removing the first and second End Sub statements as well as the Sub Macro2() and Sub RemoveNegativeRows() statements... this should allow the code to flow continuously from beginning to end. Rick "BurtArkin" wrote in message ... Below is all the code for the three actions (yours included). Sub Macro1() 'delete old data Range("A1:I33").Select 'Range("I33").Activate Range("a1").Activate Selection.ClearContents Selection.QueryTable.Delete 'enter new data With ActiveSheet.QueryTables.Add(Connection:=Array("OLE DB;Provider=Microsoft.Jet.OLEDB.4.0;Password=""""; User ID=Admin;Data Source=C:\dblink.mdb;Mode=Share Deny Write;Extended Properties=""", """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab", "ase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" _ , "jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co", "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("a1")) .CommandType = xlCmdTable .CommandText = Array("FASBTableTemp") .Name = "dblink_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceDataFile = "C:\dblink.mdb" .Refresh BackgroundQuery:=True End With Range("a1").Select Range("a1").Activate ' Macro2 this doesn't work properly End Sub Sub Macro2() 'add next year Range("A1").Select Range("f2").Select Range("f3").Select ActiveCell.FormulaR1C1 = "=+R[-1]C+1" Range("G3").Select ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]" Range("I2").Select ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]" Selection.AutoFill Destination:=Range("I2:I30"), Type:=xlFillDefault Range("I2:I30").Select Range("H2").Select Selection.AutoFill Destination:=Range("H2:H30"), Type:=xlFillDefault Range("H2:H30").Select Range("G3").Select ActiveCell.FormulaR1C1 = "=+R[-1]C[2]" Range("G3").Select Selection.AutoFill Destination:=Range("G3:G30"), Type:=xlFillDefault Range("G3:G30").Select Columns("G:j").Select Selection.Style = "Currency" Columns("G:j").EntireColumn.AutoFit Columns("G:j").Select Selection.Style = "Currency" Columns("G:j").EntireColumn.AutoFit 'fill in years automatically ActiveCell.Offset(2, -1).Range("A1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:A28"), Type:=xlFillDefault ActiveCell.Range("A1:A28").Select 'find first endyramt <0 & change recamt to correct number Set MYRANGE = Range("i2:i" & Cells(Rows.Count, "i").End(xlUp).Row) For Each c In MYRANGE If c.Value < "" And c.Value <= 0 Then c.Offset(0, -1).Value = c.Offset(0, -2).Value ' Do what you want Exit Sub End If Next 'RemoveNegativeRows this doesn't work properly End Sub Sub RemoveNegativeRows() Dim X As Long Dim LastCell As Long With Worksheets("FASBworksheet") LastCell = .Cells(Rows.Count, "I").End(xlUp).Row For X = LastCell To 1 Step -1 If .Cells(X, "I").Value = 0 Then Exit For .Cells(X, "I").EntireRow.Delete Next End With End Sub -- Burt "Rick Rothstein (MVP - VB)" wrote: It is kind of hard to say what might be wrong without seeing the code from "macro2". It could be a timing problem or it could be a logic construction blocking the continuation of code or any number of other things. One thing you might try is constructing a fourth macro which does nothing be call each of the other macros one at a time (although this could still be subject to a timing problem). If this fourth macro idea does work, or is not an acceptable solution, you will have to post your "macro2" code so we can look at it. Rick The other issue is this--I want the spreadsheet to import data, which is what "macro1" does. Then I run "macro2", and then the one you supplied. What I would like is to combine all the macros into one, or try to "call" the second and then the third. When I place your addition into "macro2" nothing happens. Am I missing a step, or is there a way to stop/start? Appreciate your help. By the way, if all this could be done while the user remains in Access, and just imports the results of the spreadsheet, that would be really perfect! Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting negative numbers
Thanks for all your help and patience. I tried what you suggested, but it
wouldn't work. When running the "combination" the procedure's second stage goes to new columns, creating columns with no numbers, just formulas, because of the shift to the right outside the selected range. I hope that's clear. It's very confusing to me. It seems as if, when starting the second section of the macro, the shift occurs even thought I tell it to go to A1 before proceeding. -- Burt "Rick Rothstein (MVP - VB)" wrote: If those 3 macros work fine when executed individually, then you should be able to "gang them together" by just removing the first and second End Sub statements as well as the Sub Macro2() and Sub RemoveNegativeRows() statements... this should allow the code to flow continuously from beginning to end. Rick "BurtArkin" wrote in message ... Below is all the code for the three actions (yours included). Sub Macro1() 'delete old data Range("A1:I33").Select 'Range("I33").Activate Range("a1").Activate Selection.ClearContents Selection.QueryTable.Delete 'enter new data With ActiveSheet.QueryTables.Add(Connection:=Array("OLE DB;Provider=Microsoft.Jet.OLEDB.4.0;Password=""""; User ID=Admin;Data Source=C:\dblink.mdb;Mode=Share Deny Write;Extended Properties=""", """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab", "ase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" _ , "jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co", "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("a1")) .CommandType = xlCmdTable .CommandText = Array("FASBTableTemp") .Name = "dblink_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceDataFile = "C:\dblink.mdb" .Refresh BackgroundQuery:=True End With Range("a1").Select Range("a1").Activate ' Macro2 this doesn't work properly End Sub Sub Macro2() 'add next year Range("A1").Select Range("f2").Select Range("f3").Select ActiveCell.FormulaR1C1 = "=+R[-1]C+1" Range("G3").Select ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]" Range("I2").Select ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]" Selection.AutoFill Destination:=Range("I2:I30"), Type:=xlFillDefault Range("I2:I30").Select Range("H2").Select Selection.AutoFill Destination:=Range("H2:H30"), Type:=xlFillDefault Range("H2:H30").Select Range("G3").Select ActiveCell.FormulaR1C1 = "=+R[-1]C[2]" Range("G3").Select Selection.AutoFill Destination:=Range("G3:G30"), Type:=xlFillDefault Range("G3:G30").Select Columns("G:j").Select Selection.Style = "Currency" Columns("G:j").EntireColumn.AutoFit Columns("G:j").Select Selection.Style = "Currency" Columns("G:j").EntireColumn.AutoFit 'fill in years automatically ActiveCell.Offset(2, -1).Range("A1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:A28"), Type:=xlFillDefault ActiveCell.Range("A1:A28").Select 'find first endyramt <0 & change recamt to correct number Set MYRANGE = Range("i2:i" & Cells(Rows.Count, "i").End(xlUp).Row) For Each c In MYRANGE If c.Value < "" And c.Value <= 0 Then c.Offset(0, -1).Value = c.Offset(0, -2).Value ' Do what you want Exit Sub End If Next 'RemoveNegativeRows this doesn't work properly End Sub Sub RemoveNegativeRows() Dim X As Long Dim LastCell As Long With Worksheets("FASBworksheet") LastCell = .Cells(Rows.Count, "I").End(xlUp).Row For X = LastCell To 1 Step -1 If .Cells(X, "I").Value = 0 Then Exit For .Cells(X, "I").EntireRow.Delete Next End With End Sub -- Burt "Rick Rothstein (MVP - VB)" wrote: It is kind of hard to say what might be wrong without seeing the code from "macro2". It could be a timing problem or it could be a logic construction blocking the continuation of code or any number of other things. One thing you might try is constructing a fourth macro which does nothing be call each of the other macros one at a time (although this could still be subject to a timing problem). If this fourth macro idea does work, or is not an acceptable solution, you will have to post your "macro2" code so we can look at it. Rick The other issue is this--I want the spreadsheet to import data, which is what "macro1" does. Then I run "macro2", and then the one you supplied. What I would like is to combine all the macros into one, or try to "call" the second and then the third. When I place your addition into "macro2" nothing happens. Am I missing a step, or is there a way to stop/start? Appreciate your help. By the way, if all this could be done while the user remains in Access, and just imports the results of the spreadsheet, that would be really perfect! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
change 2000 cells (negative numbers) into positive numbers | Excel Worksheet Functions | |||
Need to reconcile numbers accounting Harlan Grove code doesn't work for negative numbers | Excel Programming | |||
Deleting a negative sign from an amount in a list (not trailing negative) | Excel Programming |