unique id for excel cell/range objects

[[email protected]]

Hi,

I am trying to assign unique identities to the excel cell/range
objects. I searched around in the object definition of Range and found
that ID field was the only field where I could set my value. The
problem is once I set this ID on the cell or row object and then copy &
paste them, the ID's are copied as well. So I lose the identity and I
have duplicate rows.

Isn't there a UniqueID concept in Excel on the lines of Microsoft
project object (eg., task.UniqueID).

How can I uniquely identify a row/cell in the excel spread sheet.

your help in this regard is much appreciated.

regards

[Patrick Molloy]

each cell in fact does have a unique address...A1, A2 etc

it sounds liek you're trying to re-invent the wheel.

All you really need to do is save the rnage address into a text string.
Using named ranges should work too, if you use them correctly. copy data
from a named range to another region or sheet does not copy the range's name

eg if you hae a range called "My Data" - like a table

SET rTable = Range("My Data")


with worksheets("sheet2").Range("B1")
.Resize(rTable.Rows.Couint,rTable.Columns.Count).Value = rTable.Value
end with


"[email protected]" wrote:

> Hi,
>
> I am trying to assign unique identities to the excel cell/range
> objects. I searched around in the object definition of Range and found
> that ID field was the only field where I could set my value. The
> problem is once I set this ID on the cell or row object and then copy &
> paste them, the ID's are copied as well. So I lose the identity and I
> have duplicate rows.
>
> Isn't there a UniqueID concept in Excel on the lines of Microsoft
> project object (eg., task.UniqueID).
>
> How can I uniquely identify a row/cell in the excel spread sheet.
>
> your help in this regard is much appreciated.
>
> regards
>
>

[[email protected]]

Thank you very much for the quick response.

The solution above doesn't work for me because, A1, A2 etc are not
unique for cells and they change if you move around the columns as
explained below.

If you insert a new column into the sheet ie., select the column A, by
clicking on "A" and then right click and "Insert", you get a
completely new column which is A1, A2. The cells in A1, A2 now become
B1, B2.

I want to store something hidden in the cell which I can use for my
calculations.

Thanks again

[Patrick Molloy]

then why not use range names? thats what they are for.

<[email protected]> wrote in message
news:[email protected]...
> Thank you very much for the quick response.
>
> The solution above doesn't work for me because, A1, A2 etc are not
> unique for cells and they change if you move around the columns as
> explained below.
>
> If you insert a new column into the sheet ie., select the column A, by
> clicking on "A" and then right click and "Insert", you get a
> completely new column which is A1, A2. The cells in A1, A2 now become
> B1, B2.
>
> I want to store something hidden in the cell which I can use for my
> calculations.
>
> Thanks again
>

[[email protected]]

thanks again for the reply.

The problem as I already told is that the name of the cell itself
changes once you move the cell to the next column

eg., if the cell is in first row and first column, the call cell.Name
returns "$A$1"
but as soon I insert a column in the beginning the cell.Name returns
"$B$1"

Its not allowing me to change the name either.

I am not an experienced Excel user so, if you feel I am pestering with
a pretty basic question, please bear with me.

Can you give me an example of how to set the name for a cell and row so
that I can use it as a unique id.

thanks again

[Rowan]

Patrick was refering to Named Ranges not the cell.name property.

To add a name select the cell (or range) and then from the menus
Insert>Name>Define. Enter a name that makes sense (eg MyRange) and click OK.

You can then refer to this eg:

Range("MyRange").value = 5

MyRange will then always refer to this cell even if it's address is moved by
adding columns etc.

If you want to set the name via VBA use the macro recorder to get the
relevant code.

Hope this helps.
Rowan

"[email protected]" wrote:

> thanks again for the reply.
>
> The problem as I already told is that the name of the cell itself
> changes once you move the cell to the next column
>
> eg., if the cell is in first row and first column, the call cell.Name
> returns "$A$1"
> but as soon I insert a column in the beginning the cell.Name returns
> "$B$1"
>
> Its not allowing me to change the name either.
>
> I am not an experienced Excel user so, if you feel I am pestering with
> a pretty basic question, please bear with me.
>
> Can you give me an example of how to set the name for a cell and row so
> that I can use it as a unique id.
>
> thanks again
>
>

[[email protected]]

Thanks again Rowan and Patrick

I hastily replied last time without experimenting enough. I
misunderstood what Patrick was saying and was trying to set and and
print name. The set name ofcourse worked but when you print the name it
always prints the system generated name, not the one I set. Here is the
working code.
intRowCount = activesheet.UsedRange.Rows.Count
For i = 1 To intRowCount
Set row = activesheet.Rows(i)
row.Name = "row_id_1234532"
For Each cell In row.Cells
If IsNull(cell) = False And Not cell = "" Then
cell.Name = "cell_id_23223323"
End If
Next
Next i

thanks for all the help

I have one more question brewing, will post soon!!