Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique id for excel cell/range objects
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique id for excel cell/range objects
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).V alue = rTable.Value end with " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique id for excel cell/range objects
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique id for excel cell/range objects
then why not use range names? thats what they are for.
wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique id for excel cell/range objects
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique id for excel cell/range objects
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 InsertNameDefine. 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 " 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
unique id for excel cell/range objects
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
Question - How to group unique objects in Excel | Excel Discussion (Misc queries) | |||
how to dragDrop objects from other applications in 1 cell in excel | Excel Programming | |||
how to dragDrop objects from other applications in 1 cell in excel | Excel Programming | |||
Unable to remove Sheet objects in the Microsoft Excel Objects | Excel Programming |