MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Re: How to do this with macro?

• Follow

```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
dguillett@gmail.com
"Don Guillett" <dguillett@gmail.com> 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
> 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.
>>
>>
>>
>>
>>
>

```
 0

0 Replies
466 Views

Similiar Articles:

9/6/2012 12:07:42 AM