Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|