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.

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
dguillett@gmail.com
"Arto K" <arto.koivisto@noadd.fi.inv> wrote in message 
news:uz9Pn.17155$if1.13173@uutiset.elisa.fi...
>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.
>
>
>
>
> 

>> =MAX(B:B)-A1

This formula solve my problem. It's nice solution, thank you very much!



"Don Guillett" <dguillett@gmail.com> wrote in message 
news:%23wUxjdoBLHA.5848@TK2MSFTNGP06.phx.gbl...
> 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
> dguillett@gmail.com
> "Arto K" <arto.koivisto@noadd.fi.inv> wrote in message 
> news:uz9Pn.17155$if1.13173@uutiset.elisa.fi...
>>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.
>>
>>
>>
>>
>>
>