using macro to name a dynamic cell

[Guest]

I am automating a process for doing some calculations and "grading". I have
code that sums a column when data is input. However I want that sum to feed
another calculation in another location. Because the position of the cell
with the sum changes with the size of the data set, I can not refer to it by
address. I have looked at naming the cell, but the name appears to be
attached to an address. Currently, the cell with the sum is attached to cell
b1 in a defined range called "absum".

Any ideas would be much appreciated.
TIA

 

[Guest]

Hi Papa,
This assumes you have located on the cell you want to name "AbsSum" and it
first deletes any name "AbsSum", then names the cell you are located on
"AbsName".

Sub Macro1()
ActiveWorkbook.Names("AbsSum").Delete
ThisRow = ActiveCell.Row
ThisColumn = ActiveCell.Column
ActiveWorkbook.Names.Add Name:="AbsSum", RefersToR1C1:="=Sheet1!R" &
(ThisRow) & "C" & (ThisColumn)
End Sub

 

[Guest]

David,
Your intro has me a little confused. Are you saying that I need to ensure
that "AbsSum" is not used as a name anywhere else - and in order to do that I
need to add the delete statement? Is that what I am accomplishing there
before I name the active cell?

Thanks.

 

[Guest]

Thanks David. This looks like it would work. I have incorporated it. The
intermediate window indicates that "thisrow" and "thiscolumn" are working.
But the cell is not getting renamed. I've compared it to naming commands
that have specific cells and I can not see the difference.
Any ideas?

Thanks,
Papa