Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do this with macro?
I have several cells in one column, what include numbers (example B2-B4).
After few empty cells, I have a cell (example B7), what include formula B4-A1. A1 B1 1 2 3 4 5 6 xx =B4-A1 Sometimes I add new numbers after cell B4 (example B5, B6 etc..) and sometimes I have to add new lines between last used cell, what include numbers and cells, what include the formula (example B7). When I add example three new line before B7, formula moves in cell B10. On the A7 is value xx. If B7 moves B10, also A7 moves A10. After changes example look like this: A1 B1 1 2 3 4 5 6 7 8 xx =B8-A1 How to do macro, what find column A, cell what include text "xx" (example A10) and then it find last used cell in column B (example B8) and after that, the macro put formula in cell B10. The formula should be "last used cell in column B"-A1. Example in this case B8-A1. Ps. I use Excel 2007. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do this with macro?
=MAX(B:B)-A1
This formula solve my problem. It's nice solution, thank you very much! "Don Guillett" wrote in message ... Try this withOUT needing "xx" =MAX(B:B)-A1 To sum col B =SUM(B2:OFFSET(B2,COUNTA($B:$B),0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Arto K" wrote in message ... I have several cells in one column, what include numbers (example B2-B4). After few empty cells, I have a cell (example B7), what include formula B4-A1. A1 B1 1 2 3 4 5 6 xx =B4-A1 Sometimes I add new numbers after cell B4 (example B5, B6 etc..) and sometimes I have to add new lines between last used cell, what include numbers and cells, what include the formula (example B7). When I add example three new line before B7, formula moves in cell B10. On the A7 is value xx. If B7 moves B10, also A7 moves A10. After changes example look like this: A1 B1 1 2 3 4 5 6 7 8 xx =B8-A1 How to do macro, what find column A, cell what include text "xx" (example A10) and then it find last used cell in column B (example B8) and after that, the macro put formula in cell B10. The formula should be "last used cell in column B"-A1. Example in this case B8-A1. Ps. I use Excel 2007. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |