Insert Rows Macro
 

I am using a insert rows macro which carries formulas when you insert a row. I have two relative formulas and one absolute formula. One of my relative formulas is =IF(D11=1, A11+1,""), whenever I add rows with my macro D11 and A11 stay the same. I need them to change for the next row.

Insert Rows Macro
 

Jason,

The macro below shows the proper technique, copying the row of the
activecell and inserting a new row at the activecell.

HTH,
Bernie
MS Excel MVP

Sub InsertCopiedRow()

' Macro 1/5/2004 by Bernie Deitrick

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

"Jason Watts" wrote in message
...
I am using a insert rows macro which carries formulas when you
insert a row. I have two relative formulas and one absolute formula.
One of my relative formulas is =IF(D11=1, A11+1,""), whenever I add
rows with my macro D11 and A11 stay the same. I need them to change
for the next row.

Insert Rows Macro
 

Thanks Bernie, but when I insert the new row the only info I want copied are my formulas, I already have a macro that works for that, except the IF formula copies like it is absolute.

----- Bernie Deitrick wrote: -----

Jason,

The macro below shows the proper technique, copying the row of the
activecell and inserting a new row at the activecell.

HTH,
Bernie
MS Excel MVP

Sub InsertCopiedRow()

' Macro 1/5/2004 by Bernie Deitrick

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

"Jason Watts" wrote in message
...
I am using a insert rows macro which carries formulas when you
insert a row. I have two relative formulas and one absolute formula.
One of my relative formulas is =IF(D11=1, A11+1,""), whenever I add
rows with my macro D11 and A11 stay the same. I need them to change
for the next row.

Insert Rows Macro
 

Post your code and maybe someone can see what the problem is.

--
Regards,
Tom Ogilvy

"Jason Watts" wrote in message
...
Thanks Bernie, but when I insert the new row the only info I want copied
are my formulas, I already have a macro that works for that, except the IF
formula copies like it is absolute.

----- Bernie Deitrick wrote: -----

Jason,

The macro below shows the proper technique, copying the row of the
activecell and inserting a new row at the activecell.

HTH,
Bernie
MS Excel MVP

Sub InsertCopiedRow()

' Macro 1/5/2004 by Bernie Deitrick

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

"Jason Watts" wrote in message
...
I am using a insert rows macro which carries formulas when you
insert a row. I have two relative formulas and one absolute formula.
One of my relative formulas is =IF(D11=1, A11+1,""), whenever I add
rows with my macro D11 and A11 stay the same. I need them to change
for the next row.

Insert Rows Macro
 

Sub InsertRowsAndFillFormulas()
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Insert Rows -- 1997/09/24 Mark Hill
'Dim vRows As Integer
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows < 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedShee ts
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select

End Sub

Hope someone can help!!

----- Tom Ogilvy wrote: -----

Post your code and maybe someone can see what the problem is.

--
Regards,
Tom Ogilvy

"Jason Watts" wrote in message
...
Thanks Bernie, but when I insert the new row the only info I want copied
are my formulas, I already have a macro that works for that, except the IF
formula copies like it is absolute.
----- Bernie Deitrick wrote: -----
Jason,
The macro below shows the proper technique, copying the row of the
activecell and inserting a new row at the activecell.
HTH,
Bernie
MS Excel MVP
Sub InsertCopiedRow()
' Macro 1/5/2004 by Bernie Deitrick
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub
"Jason Watts" wrote in message
...
I am using a insert rows macro which carries formulas when you
insert a row. I have two relative formulas and one absolute formula.
One of my relative formulas is =IF(D11=1, A11+1,""), whenever I add
rows with my macro D11 and A11 stay the same. I need them to change
for the next row.

Insert Rows Macro
 

Jason,

Your subroutine worked fine for me. To simplify and find the problem,
try this:

Sub InsertCopiedRow()
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown

ActiveCell(2).EntireRow.SpecialCells(xlCellTypeCon stants).ClearContent
s
End Sub

If you still get 'absolute' style formulas, then something else may be
wrong.

HTH,
Bernie
MS Excel MVP

"Jason Watts" wrote in message
...
Thanks Bernie, but when I insert the new row the only info I want
copied are my formulas, I already have a macro that works for that,
except the IF formula copies like it is absolute.

----- Bernie Deitrick wrote: -----

Jason,

The macro below shows the proper technique, copying the row of
the
activecell and inserting a new row at the activecell.

HTH,
Bernie
MS Excel MVP

Sub InsertCopiedRow()

' Macro 1/5/2004 by Bernie Deitrick

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

"Jason Watts" wrote in
message
...
I am using a insert rows macro which carries formulas when
you
insert a row. I have two relative formulas and one absolute
formula.
One of my relative formulas is =IF(D11=1, A11+1,""), whenever
I add
rows with my macro D11 and A11 stay the same. I need them to
change
for the next row.

Insert Rows Macro
 

The formula below is where the 4 & 5 are at whenever I add a new row with my macro the D9 & A9 should become D10 & A10, they don't they stay the same.
=IF(D9=1, A9+1,""
4 Flash 10' 10 2 20.00 $400.00
4 50 3 150.00 $3,000.00
5
5 60 4 240.00 $4,800.00

Insert Rows Macro
 

But: did you try my macro, and did it make a difference or not?

HTH,
Bernie
MS Excel MVP

"Jason Watts" wrote in message
...
The formula below is where the 4 & 5 are at whenever I add a new row
with my macro the D9 & A9 should become D10 & A10, they don't they
stay the same.
=IF(D9=1, A9+1,"")
4 Flash 10' 10 2 20.00 $400.00 3
4 50 3 150.00 $3,000.00 1
5
5 60 4 240.00 $4,800.00 1

Insert Rows Macro
 

The macro worked fine for me. It adjusted the row references in the formula
you showed: =IF(D11=1, A11+1,"")

Regards,
Tom Ogilvy

"Jason Watts" wrote in message
...
Sub InsertRowsAndFillFormulas()
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Insert Rows -- 1997/09/24 Mark Hill
'Dim vRows As Integer
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to preselect entire
row
If vRows < 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedShee ts
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee
2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select

End Sub

Hope someone can help!!

----- Tom Ogilvy wrote: -----

Post your code and maybe someone can see what the problem is.

--
Regards,
Tom Ogilvy

"Jason Watts" wrote in message
...
Thanks Bernie, but when I insert the new row the only info I want
copied
are my formulas, I already have a macro that works for that, except
the IF
formula copies like it is absolute.
----- Bernie Deitrick wrote: -----
Jason,
The macro below shows the proper technique, copying the row
of the
activecell and inserting a new row at the activecell.
HTH,
Bernie
MS Excel MVP
Sub InsertCopiedRow()
' Macro 1/5/2004 by Bernie Deitrick
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub
"Jason Watts" wrote in
message
...
I am using a insert rows macro which carries formulas when you
insert a row. I have two relative formulas and one absolute
formula.
One of my relative formulas is =IF(D11=1, A11+1,""), whenever
I add
rows with my macro D11 and A11 stay the same. I need them to
change
for the next row.

Insert Rows Macro
 

Bernie I tried your macro and it did the same thing.

Insert Rows Macro
 

I went back over every thing again and I think I didn't explain my problem right. The Macro does adjust the row references in the new inserted row however the existing rows after do not adjust thus my count is off ie. 1,2,3,3,4. How do I make the second 3 a 4. Hope this makes more sence.

Insert Rows Macro
 

Jason,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myCell As Range

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown

ActiveCell(2).EntireRow.SpecialCells(xlCellTypeCon stants).ClearContent
s

For Each myCell In ActiveCell(0).EntireRow.SpecialCells(xlFormulas)
myCell.Copy Range(myCell, Cells(65536, myCell.Column).End(xlUp))
Next myCell
Application.CutCopyMode = False
End Sub

"Jason Watts" wrote in message
...
I went back over every thing again and I think I didn't explain my
problem right. The Macro does adjust the row references in the new
inserted row however the existing rows after do not adjust thus my
count is off ie. 1,2,3,3,4. How do I make the second 3 a 4. Hope
this makes more sence.

Insert Rows Macro
 

Bernie, it works except there is a bug in the following line,
myCell.Copy Range(myCell, Cells(65536, myCell.Column).End(xlUp))

Insert Rows Macro
 

Worked for me.

Perhaps you're using Excel 95? Try

myCell.Copy Range(myCell, Cells(5000, myCell.Column).End(xlUp))

If that doesn't work, then change to this and run the whole macro to
help debug:

Sub TryNow()
Dim myCell As Range

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown

ActiveCell(2).EntireRow. _
SpecialCells(xlCellTypeConstants).ClearContents

For Each myCell In ActiveCell(0).EntireRow.SpecialCells(xlFormulas)
MsgBox myCell.Address
myCell.Copy
MsgBox Range(myCell, Cells(65536, myCell.Column).End(xlUp)).Address
Range(myCell, Cells(65536, myCell.Column).End(xlUp)). _
PasteSpecial (xlPasteFormulas)
Next myCell
Application.CutCopyMode = False
End Sub

Report specifically what errors you get.

HTH,
Bernie
MS Excel MVP

"Jason Watts" wrote in message
...
Bernie, it works except there is a bug in the following line,
myCell.Copy Range(myCell, Cells(65536, myCell.Column).End(xlUp))

Insert Rows Macro
 

Bernie, I am using excel xp, I tried your new macro and got a Run-time error '1004': No cells were found. Hope this helps.

Insert Rows Macro
 

Jason,

If no cells were found, then either you don't have any cells with
formulas, or you don't have any cells with constants. In either case,
after the line:

Dim myCell As Range

put this line:

On Error Resume Next.

HTH,
Bernie
MS Excel MVP

"Jason Watts" wrote in message
...
Bernie, I am using excel xp, I tried your new macro and got a
Run-time error '1004': No cells were found. Hope this helps.

Insert Rows Macro
 

Still cant get it to work the, On Error Resume Next. line gave me an error.

Insert Rows Macro
 

If a macro terminates after turning calculation off, but before
the macro would turn it back on again, you can end up
with calculation turned off. Same applies to an addin.
The only difference is that addins are usually written to be
more bulletproof -- doesn't mean they always are. The usual
cause is from testing your own macros and they blow up.

If you have a lot of problems with something turning calculation
off and don't know what it is you might look at the task bar and
see if you see calculate or something similar stuck there after
everything has completed. A better solution toward identifying
when it happens is to test for it -- see Auto_open macro in
http://www.mvps.org/dmcritchie/excel...esp.htm#addins

If you look at the page referenced in your original macro you
would see another technique specifically addressing rows
added to the bottom (extending). Though I think this was
added in Excel 2000 -- I use Excel 2000 and never used Excel 97.

Extended Formats and Formulas (#extended)
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel...w.htm#extended

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jason Watts" wrote ...
The formula below is where the 4 & 5 are at whenever I add a new
row with my macro the D9 & A9 should become D10 & A10, they don't they stay the same.
=IF(D9=1, A9+1,"")
4 Flash 10' 10 2 20.00 $400.00 3
4 50 3 150.00 $3,000.00 1
5
5 60 4 240.00 $4,800.00 1

Insert Rows Macro
 

Jason,

Ooooops! I accidentally hit a period after Next

On Error Resume Next.
should be
On Error Resume Next

Sorry about that.

Bernie
MS Excel MVP

"Jason Watts" wrote in message
...
Still cant get it to work the, On Error Resume Next. line gave me an
error.

Insert Rows Macro
 

~× wrote:
*I am using a insert rows macro which carries formulas when yo
insert a row. I have two relative formulas and one absolute formula
One of my relative formulas is =IF(D11=1, A11+1,""), whenever I ad
rows with my macro D11 and A11 stay the same. I nee
d them to change for the next row. *

Try,
=IF($D11=1, $A11+1,"") to reflect different rows same column,
=IF(D$11=1, A$11+1,"") to reflect different columns same row,
=IF($D$11=1, $A$11+1,"") to maintain constant cell

--
Message posted from http://www.ExcelForum.com