oops. to find the last number in b and subtract a1

=INDEX(B:B,MATCH(9999999,B:B))-A1

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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.