Posts by michibahn

    Re: Auto copy column formula to the new row


    Hi EggCel and all.. Ok I guess I'm gonna bother you again because of a new requirement. The users of the table now want to group the entries based on a unique code in column A and create a SUB TOTAL per each unique set. I don't know if this is workable. The difficult thing is the the formula in column H is not a continuous copy from the top or below row. Instead if a new group is separated, the next group will start its own calculation and copy the formula only within its group.


    I just don't know if I explained it clearly but I'm attaching the spreadsheet to see how they want it to be.


    Thanks again!

    Re: Auto copy column formula to the new row


    Hi EggCel.. I'm just gonna revoke my reply above. I think the code works perfectly fine. What I did is just delete the pasted formulas until row 10074 and corrected the formula of the TOTAL row and saved the file. Now, whenever a new row is inserted, the formulas in rows 10 onwards (except the very last row) is copied fine in column H and the TOTAL row retained its correct formula in column H.


    I can't thank you enough for creating this code for me :):) thank you so much!

    Re: Auto copy column formula to the new row


    Hi EggCel.. column H from row 10 onwards has only the formulas that needs to be copied except for the very last row whihc is the TOTAL and has a different formula in column H. the table will just go on as the new rows are inserted.


    I think the new code is messed up compared to the last one. When I inserted a new 2 rows, it infinitley copied the formula on column H until row 10074. My goal is to stop copying the formula starting from the very last row which is the "TOTAL" and onwards. By the way, the TOTAL row (very last row) formula in column H is (very last row value in column F - very last row value in column G)


    See the attached snapshot.

    Re: Auto copy column formula to the new row


    Hi EggCel.. that did the trick on the row 9 and above. One last, can the code not apply to the very last row of the table with data? because my last row has a different formula and different label "TOTAL" and also if I made a change in the new rows below, I couldn't erase them it'll just enter the formulas in column H since it treats them as a worksheet_change.


    Here's my final table looks like.


    thanks again

    Re: Auto copy column formula to the new row


    Hi EggCel.. code seems to do its job. However, can it skip copying formulas startiing row #9 and up and the last row with data? because my Row #9 and above are pure texts and table names and my last row has a grand total which has a separate formula


    And can you teach me how to do this using macro? I can't do much in the table since every bit of changes in the table, it copies formula in column H and cannot even delete a row now it'll just inserts the formula in column H.


    Attached is what my final table looks like..


    Thanks so much for your help :)

    Re: Auto copy column formula to the new row


    Hi EggCel.. sorry to bother you again. I found something unusual with the code. If only 1 new row is inserted, it works perfectly fine. However, if 2 new rows are inserted, the 2nd and 3rd rows from the last row doesn't have formula in Column H. If 3 new rows are inserted, 2nd, 3rd, and 4th rows from the last row doesn't have formula.


    So to summarize, if 2 new rows is inserted, I will get 2 blank cells in colum H starting from the 2nd row from the last row and up. If 3 new rows inserted, I will get 3 blank cells in column H starting also from 2nd row from the last row and up. This scenario is encountered on the number of new rows inserted in the table.


    Attached is the snapshot of what I'm trying to explain.


    Can there be any adjustment in the code? I really appreciate your help.


    TIA!

    Hi guys, this forum has been helpful to me.. it just solved 1 problem of mine with the help of EggCell..


    Anyway, as the title says, can I create a macro in excel to run a macro in ACCESS? My goal is just to open my excel file and run my ACCESS macro without opening my ACCESS program.


    TIA!

    Re: Auto copy column formula to the new row


    Thanks EggCell! The code worked perfectly when i do manually insert a new row. However, this is not the way how the new rows are inserted. The new rows are inserted from an external data which is an MS Access query. (I guess I should have mentioned this from the start)


    If I run the import data, the rows are inserted but the formulas in column H are not populated :( because the import from ms access query sometimes imports multiple rows maybe the code doesn't cope up with the activity.


    I'm now thinking if a there is a code that will do a "copy cell" say starting from H10 upto the last row that has data in it..


    All I want is just calculate the H column automatically when a new row/s is inserted from an MS access query.

    Re: Auto copy column formula to the new row


    Hi EggCel... thank you for creating the code. I tried to insert it in my worksheet module and the formula is automatically inserted now in the H column of the new row. However, the formula in the H column below the new row is not updated. Everything below the new row should be incremented by 1 to make the computation correct.


    example:
    new row inserted in row 18 - H column shows =SUM(H17,F18,-G18) >> this is correct :)
    in row 19 - H column still shows =SUM(H17,F18,-G18) >> this should be now =SUM(H18,F19,-G19) and so on until the last row with data.


    I hope this can be done to complete my goal..


    thanks for your help

    I'm working on a spreadsheet wherein I need to auto copy the formula from the above cell to the new cell below which is technically a new row.


    Attached is a snapshot on what I have in excel using the formula in column "H" =SUM(H2,F3,-G3) to calculate the BALANCE column.


    So based on my example, if a new row is inserted in Row 8 the formula in cell H8 should be =SUM(H7,F8,-G8) this should be automatically inserted.


    Is there a way I can do this automatically?


    TIA!