Help Help Help!!

  • Thread starter Thread starter sgrech
  • Start date Start date
S

sgrech

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
 
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" <[email protected]>
wrote in message
news:[email protected]...
 
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
 
You could give the cell a name and refer to that name in your code
instead of its address.
 
Cutter said:
You could give the cell a name and refer to that name in your code
instead of its address.
Click to expand...

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

Thanks
 
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 said:
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
Click to expand...

thank you very much
 
Back
Top