Is this possbile?

I have a spreadsheet which has a cell e.g. A5 which represents the
total of the cells above e.g. A1:A4. When I finish updating my
spreadsheet I run a simple macro which takes the total and copies the
value to another cell e.g. B1. Now here's the problem. On occassion I
may need to total more than four cells and therefore I have insert rows
to add the extra information. However when I run the macro the wrong
value is picked up as the cell with the total has now moved.

Is anyone able to suggest a solution to this?

Thanks a lot.

Simon


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=504181

If your total cell is always the last cell in column A, use code
like

Dim TotalCell As Range
Set TotalCell = Cells(Rows.Count, "A").End(xlUp)
Range("B1").Value = TotalCell.Value
' or more simply
Range("B1").Value = Cells(Rows.Count, "A").End(xlUp)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"sgrech"
wrote in message
...

Is this possbile?

I have a spreadsheet which has a cell e.g. A5 which represents
the
total of the cells above e.g. A1:A4. When I finish updating my
spreadsheet I run a simple macro which takes the total and
copies the
value to another cell e.g. B1. Now here's the problem. On
occassion I
may need to total more than four cells and therefore I have
insert rows
to add the extra information. However when I run the macro the
wrong
value is picked up as the cell with the total has now moved.

Is anyone able to suggest a solution to this?

Thanks a lot.

Simon


--
sgrech
------------------------------------------------------------------------
sgrech's Profile:
http://www.excelforum.com/member.php...o&userid=14501
View this thread:
http://www.excelforum.com/showthread...hreadid=504181

Hi Chip,
Thanks for you reply, unfortunately though there are also values in the
cells below the total. Any more ideas how to deal with this?

Cheers
Simon


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=504181

You could give the cell a name and refer to that name in your code
instead of its address.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=504181

Cutter Wrote:
You could give the cell a name and refer to that name in your code
instead of its address.

Sorry for the dumb question, but what would be the code for this?

Thanks


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=504181

Your code would be the same as what you're using now except you would
use your defined name instead of the cell address

eg. Name your A5 cell as Total_Value

use Range("Total_Value") instead of Range("A5") in your code


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=504181

Cutter Wrote:
Your code would be the same as what you're using now except you would
use your defined name instead of the cell address

eg. Name your A5 cell as Total_Value

use Range("Total_Value") instead of Range("A5") in your code

thank you very much


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=504181

You're welcome. I assume you've got it working now.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=504181