Help Help Help!!

[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

 

[Chip Pearson]

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]...

 

[sgrech]

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

 

[Cutter]

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

 

[sgrech]

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

 

[Cutter]

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

 

[sgrech]

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

 

[Cutter]

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