Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
sgrech
 
Posts: n/a
Default Help Help Help!!


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

  #2   Report Post  
Posted to microsoft.public.excel.newusers
Chip Pearson
 
Posts: n/a
Default Help Help Help!!

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



  #3   Report Post  
Posted to microsoft.public.excel.newusers
sgrech
 
Posts: n/a
Default Help Help Help!!


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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
Cutter
 
Posts: n/a
Default Help Help Help!!


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

  #5   Report Post  
Posted to microsoft.public.excel.newusers
sgrech
 
Posts: n/a
Default Help Help Help!!


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



  #6   Report Post  
Posted to microsoft.public.excel.newusers
Cutter
 
Posts: n/a
Default Help Help Help!!


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

  #7   Report Post  
Posted to microsoft.public.excel.newusers
sgrech
 
Posts: n/a
Default Help Help Help!!


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

  #8   Report Post  
Posted to microsoft.public.excel.newusers
Cutter
 
Posts: n/a
Default Help Help Help!!


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"