copy equation to new row

W

Wonderer

I have created a basic check book ledger and I use a macro to add a row when
I need more space. It adds the row ok, but it does not add the equation from
the "balance" column to the balance cell in the new row. How do I accomplish
that? Here is an example of the equations. I add the row to the top, so that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
 
K

K Dales

It appears that column H has the balance formula - correct? Assuming that is
so, then put this line of code someplace after you insert the new line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)
 
W

Wonderer

I do not understand what you mean by "put this line of code someplace after
you insert the new line" Please explain or give an example.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
K Dales said:
It appears that column H has the balance formula - correct? Assuming that
is
so, then put this line of code someplace after you insert the new line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)

--
- K Dales


Wonderer said:
I have created a basic check book ledger and I use a macro to add a row
when
I need more space. It adds the row ok, but it does not add the equation
from
the "balance" column to the balance cell in the new row. How do I
accomplish
that? Here is an example of the equations. I add the row to the top, so
that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Click to expand...
Click to expand...
 
K

K Dales

Sorry if not clear: it really doesn't matter where it goes as long as it is
after this:

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

So right after that you could put:

' copies the formula:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"
--
- K Dales


Wonderer said:
I do not understand what you mean by "put this line of code someplace after
you insert the new line" Please explain or give an example.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
K Dales said:
It appears that column H has the balance formula - correct? Assuming that
is
so, then put this line of code someplace after you insert the new line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)

--
- K Dales


Wonderer said:
I have created a basic check book ledger and I use a macro to add a row
when
I need more space. It adds the row ok, but it does not add the equation
from
the "balance" column to the balance cell in the new row. How do I
accomplish
that? Here is an example of the equations. I add the row to the top, so
that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Click to expand...
Click to expand...
Click to expand...
 
W

Wonderer

I copied it in, right where you said to, but it still does not enter the
formula. H3 remains blank.

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

K Dales said:
Sorry if not clear: it really doesn't matter where it goes as long as it
is
after this:

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

So right after that you could put:

' copies the formula:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"
--
- K Dales


Wonderer said:
I do not understand what you mean by "put this line of code someplace
after
you insert the new line" Please explain or give an example.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
K Dales said:
It appears that column H has the balance formula - correct? Assuming
that
is
so, then put this line of code someplace after you insert the new line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)

--
- K Dales


:

I have created a basic check book ledger and I use a macro to add a
row
when
I need more space. It adds the row ok, but it does not add the
equation
from
the "balance" column to the balance cell in the new row. How do I
accomplish
that? Here is an example of the equations. I add the row to the top,
so
that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to
the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Click to expand...
Click to expand...
Click to expand...
 
W

Wonderer

Part of the problem was that it was not saving the code, for some reason. I
got it to save and tried it again and the following error occured

run-time error '1004':
Method 'range' of object '_Global' failed

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

K Dales said:
Sorry if not clear: it really doesn't matter where it goes as long as it
is
after this:

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

So right after that you could put:

' copies the formula:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"
--
- K Dales


Wonderer said:
I do not understand what you mean by "put this line of code someplace
after
you insert the new line" Please explain or give an example.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
K Dales said:
It appears that column H has the balance formula - correct? Assuming
that
is
so, then put this line of code someplace after you insert the new line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)

--
- K Dales


:

I have created a basic check book ledger and I use a macro to add a
row
when
I need more space. It adds the row ok, but it does not add the
equation
from
the "balance" column to the balance cell in the new row. How do I
accomplish
that? Here is an example of the equations. I add the row to the top,
so
that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to
the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Click to expand...
Click to expand...
Click to expand...
 
L

Laurie

I have an old Excel file that achieves this by hiding a row containing only
formulae above the data table (i.e. between the column headers and the 1st
piece of data). You may be able to adapt it work with your sheet.


Wonderer said:
Part of the problem was that it was not saving the code, for some reason. I
got it to save and tried it again and the following error occured

run-time error '1004':
Method 'range' of object '_Global' failed

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

K Dales said:
Sorry if not clear: it really doesn't matter where it goes as long as it
is
after this:

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

So right after that you could put:

' copies the formula:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"
--
- K Dales


Wonderer said:
I do not understand what you mean by "put this line of code someplace
after
you insert the new line" Please explain or give an example.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
It appears that column H has the balance formula - correct? Assuming
that
is
so, then put this line of code someplace after you insert the new line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)

--
- K Dales


:

I have created a basic check book ledger and I use a macro to add a
row
when
I need more space. It adds the row ok, but it does not add the
equation
from
the "balance" column to the balance cell in the new row. How do I
accomplish
that? Here is an example of the equations. I add the row to the top,
so
that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to
the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Fogleman

Range("H3") , notice the Quotes.

Range("H3").Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"

Mike F

Wonderer said:
Part of the problem was that it was not saving the code, for some reason.
I got it to save and tried it again and the following error occured

run-time error '1004':
Method 'range' of object '_Global' failed

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

K Dales said:
Sorry if not clear: it really doesn't matter where it goes as long as it
is
after this:

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

So right after that you could put:

' copies the formula:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"
--
- K Dales


Wonderer said:
I do not understand what you mean by "put this line of code someplace
after
you insert the new line" Please explain or give an example.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
It appears that column H has the balance formula - correct? Assuming
that
is
so, then put this line of code someplace after you insert the new
line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)

--
- K Dales


:

I have created a basic check book ledger and I use a macro to add a
row
when
I need more space. It adds the row ok, but it does not add the
equation
from
the "balance" column to the balance cell in the new row. How do I
accomplish
that? Here is an example of the equations. I add the row to the top,
so
that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to
the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Click to expand...
Click to expand...
Click to expand...
 
W

Wonderer

(humor) Ok, I will just use my world famous psychic powers to determine what
your setup looks like and copy it to mine. :)

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Laurie said:
I have an old Excel file that achieves this by hiding a row containing only
formulae above the data table (i.e. between the column headers and the 1st
piece of data). You may be able to adapt it work with your sheet.


Wonderer said:
Part of the problem was that it was not saving the code, for some reason.
I
got it to save and tried it again and the following error occured

run-time error '1004':
Method 'range' of object '_Global' failed

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

K Dales said:
Sorry if not clear: it really doesn't matter where it goes as long as
it
is
after this:

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

So right after that you could put:

' copies the formula:
Range(H3).Formula =
"=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"
--
- K Dales


:

I do not understand what you mean by "put this line of code someplace
after
you insert the new line" Please explain or give an example.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
It appears that column H has the balance formula - correct?
Assuming
that
is
so, then put this line of code someplace after you insert the new
line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)

--
- K Dales


:

I have created a basic check book ledger and I use a macro to add a
row
when
I need more space. It adds the row ok, but it does not add the
equation
from
the "balance" column to the balance cell in the new row. How do I
accomplish
that? Here is an example of the equations. I add the row to the
top,
so
that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to
the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Click to expand...
Click to expand...
Click to expand...
 
K

K Dales

Yes, Mike is right - very sloppy on my part, sorry!
--
- K Dales


Mike Fogleman said:
Range("H3") , notice the Quotes.

Range("H3").Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"

Mike F

Wonderer said:
Part of the problem was that it was not saving the code, for some reason.
I got it to save and tried it again and the following error occured

run-time error '1004':
Method 'range' of object '_Global' failed

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

K Dales said:
Sorry if not clear: it really doesn't matter where it goes as long as it
is
after this:

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

So right after that you could put:

' copies the formula:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)"
--
- K Dales


:

I do not understand what you mean by "put this line of code someplace
after
you insert the new line" Please explain or give an example.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
It appears that column H has the balance formula - correct? Assuming
that
is
so, then put this line of code someplace after you insert the new
line:
Range(H3).Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)

--
- K Dales


:

I have created a basic check book ledger and I use a macro to add a
row
when
I need more space. It adds the row ok, but it does not add the
equation
from
the "balance" column to the balance cell in the new row. How do I
accomplish
that? Here is an example of the equations. I add the row to the top,
so
that
my most recent balance is seen upon opening the sheet.

=IF(AND(ISBLANK(E4),ISBLANK(G4)),"",H5+E4-G4)
=IF(AND(ISBLANK(E5),ISBLANK(G5)),"",H6+E5-G5)
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",H7+E6-G6)
=IF(AND(ISBLANK(E7),ISBLANK(G7)),"",H8+E7-G7)

The code that I use to add the row is as follows. What do I need to
the
code, to get it to add in the equation as well?

Sub Insert_Row()

'sets the password
Const Password = "check" '**Change password here**

'unprotectes the sheet
Sheet1.Unprotect Password:=Password

'inserts the row
Rows("3:3").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromRightOrBelow

'selects cell B3
[B3].Select

'protectes the sheet
Sheet1.Protect Password:=Password
Sheet1.EnableSelection = xlUnlockedCells
End Sub

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ongoing Issue - I have tried to clarify so that you understand 7
Recorded macro selection widened 4
An easier way? 8
Gerneralising/autofilling an Equation 1
drag formula down making chgs on the way (Q2) 5
Aiuto programmino con array 0
inserting a row WITH any needed formulas 3
How do I create a YTD table which compares production for 4 facil. 0

Top