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

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" <(E-Mail Removed)> wrote in message
news:E6D8172E-95FD-4BA5-8C6B-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
> news:E6D8172E-95FD-4BA5-8C6B-(E-Mail Removed)...
> >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
>
>

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" <(E-Mail Removed)> wrote in message
news:2665F1A5-4CB3-45A7-BE5C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:E6D8172E-95FD-4BA5-8C6B-(E-Mail Removed)...
>> >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
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:2665F1A5-4CB3-45A7-BE5C-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:E6D8172E-95FD-4BA5-8C6B-(E-Mail Removed)...
> >> >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
> >>
> >>
>
>

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

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("OLEDB;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 OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDBatab", "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 OLEDBon'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
>
>

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" <(E-Mail Removed)> wrote in message
news:A9E416EF-89AE-4CB1-8E1D-(E-Mail Removed)...
> 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("OLEDB;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 OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
> OLEDBatab", "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 OLEDBon'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
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:A9E416EF-89AE-4CB1-8E1D-(E-Mail Removed)...
> > 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("OLEDB;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 OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
> > OLEDBatab", "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 OLEDBon'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
> >>
> >>
>
>

I'm not sure if I will be able to help you then. I have almost no experience
with database manipulations, so I am not totally sure what is going on in
your first macro (explaining it to me probably won't help much, sorry).
However, all I can repeat is... if those macros work when executed
one-at-a-time, I can see no reason why they can't be ganged together as I
indicated earlier, other than perhaps a timing issue (macro1 hasn't finished
executing before macro2 begins; although that seems very unlikely). Just to
rule out a timing issue, try putting a DoEvents statement just before the
code from macro2 (and, if there is still a problem, put a DoEvents in front
of the code from my macro as well). I don't expect this will really solve
the problem; it's more of a "I can't think of any thing else" within the
realm of the non-database side of your code. As this thread is getting far
down the list of postings in this newsgroup, if the above doesn't solve your
problem, I would suggest you re-post your question (updating it with what
has transpired in this thread) in the hope that someone who missed it the
first time (and who has familiarity with the database side of things) will
see it and respond.

Rick


"BurtArkin" <(E-Mail Removed)> wrote in message
news:71C7244C-00BF-4C8C-8119-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:A9E416EF-89AE-4CB1-8E1D-(E-Mail Removed)...
>> > 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("OLEDB;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 OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet
>> > OLEDBatab", "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 OLEDBon'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
>> >>
>> >>
>>
>>