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
(E-Mail Removed)
"Arto K" <(E-Mail Removed)> wrote in message
news:uz9Pn.17155$(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
> (E-Mail Removed)
> "Arto K" <(E-Mail Removed)> wrote in message
> news:uz9Pn.17155$(E-Mail Removed)...
>>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.
>>
>>
>>
>>
>>
>