|
copy equation to new row
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 |
copy equation to new row
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" wrote: 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 |
copy equation to new row
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" wrote in message ... 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" wrote: 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 |
copy equation to new row
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" wrote: 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" wrote in message ... 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" wrote: 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 |
copy equation to new row
Yes, Mike is right - very sloppy on my part, sorry!
-- - K Dales "Mike Fogleman" wrote: Range("H3") , notice the Quotes. Range("H3").Formula = "=IF(AND(ISBLANK(E3),ISBLANK(G3)),"",H4+E3-G3)" Mike F " Wonderer" wrote in message ... 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 |
| All times are GMT +1. The time now is 10:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com