unique identifiers needed urgently!!! please help!

[ClimoC]

Hey everyone - This one's rattling my brain knackered - so I thought I'd repost this in clearer english to try and bring about an answer...

What I need is for column X in Sheet2! to have a number plopped in it everytime this macro is run. The hard part has been done - it already identifies the next blank row based on a mandatory field. Now that I have the row and can copy and paste data into the appropriate fields at will, I now just need something that will assign a number into column X when it has found that row.

I also need the numbers to grow sequentially - can someone please edit this formula to include the auto-adding of these unique identifiers?

Thanks in Advance, you attractive-looking-geniuses...
C

Sub publish2()

With Worksheets("Sheet2")
.Visible = True
End With
Application.ScreenUpdating = False
Sheets("Sheet2").Select
Columns("E:E").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate

reviewrow = ActiveCell.Row

Range("Sheet2!E" & reviewrow) = Range("Sheet1!d4").Value
Range("Sheet2!D" & reviewrow) = Range("Sheet1!D5").Value
Range("Sheet2!I" & reviewrow) = Range("Sheet1!d9").Value
Range("Sheet2!J" & reviewrow) = Range("Sheet1!d11").Value
Range("Sheet2!k" & reviewrow) = Range("Sheet1!d13").Value
Range("Sheet2!s" & reviewrow) = Range("Sheet1!d15").Value
Range("Sheet2!c" & reviewrow) = Range("Sheet1!d17").Value
Range("Sheet2!f" & reviewrow) = Range("Sheet1!g15").Value
Range("Sheet2!l" & reviewrow) = Range("Sheet1!d21").Value
Range("Sheet2!u" & reviewrow) = Range("Sheet1!d24").Value
Range("Sheet2!n" & reviewrow) = Range("Sheet1!d29").Value
Range("Sheet2!q" & reviewrow) = Range("sheet1!d31").Value
Range("Sheet2!o" & reviewrow) = Range("sheet1!g29").Value
Sheets("Sheet1").Select
Range("D9:G9,D11:G11,D13:G13,D15,G15,D21,G21,D24:G27,D5,D6,D31,G29,D29").Clearcontents
Range("d4").Select


With Worksheets("Sheet2").Visible = False
End With
Application.ScreenUpdating = True


End Sub

 

[Cindy Ellis]

Hi,
You're more likely to get useful help if you enclose your code in code tags:
"["CODE"]" and "["/CODE"]" (without the quotes), and if you don't change the font to something completely illegible.
Also, given your apparent question (regarding unique identifiers) the code seems entirely irrelevant.
So, for clarification:

• Are you looking for a value in a specific cell in column X to be incremented (presumably by 1) each time the macro is run? or...
• Are you looking for a value to be appended to a column of existing numbers in Column X? (For instance, if the last cell in row X is X5, then the new value would be added to row X6?
• Are there any constraints on the format of the "unique identifier"?
• If it's the second option above (unique identifier inserted in the next available row of column X), would it work to just use the row number?
• What relationship does the unique identifier have to the code in your question?

Answers to these and other questions may help us provide an answer.
Cindy

 

[ClimoC]

Hi Cindy

here's the code again

Sub publish2()

With Worksheets("Sheet2")
.Visible = True
End With
Application.ScreenUpdating = False
Sheets("Sheet2").Select
Columns("E:E").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate

reviewrow = ActiveCell.Row

Range("Sheet2!E" & reviewrow) = Range("Sheet1!d4").Value
Range("Sheet2!D" & reviewrow) = Range("Sheet1!D5").Value
Range("Sheet2!I" & reviewrow) = Range("Sheet1!d9").Value
Range("Sheet2!J" & reviewrow) = Range("Sheet1!d11").Value
Range("Sheet2!k" & reviewrow) = Range("Sheet1!d13").Value
Range("Sheet2!s" & reviewrow) = Range("Sheet1!d15").Value
Range("Sheet2!c" & reviewrow) = Range("Sheet1!d17").Value
Range("Sheet2!f" & reviewrow) = Range("Sheet1!g15").Value
Range("Sheet2!l" & reviewrow) = Range("Sheet1!d21").Value
Range("Sheet2!u" & reviewrow) = Range("Sheet1!d24").Value
Range("Sheet2!n" & reviewrow) = Range("Sheet1!d29").Value
Range("Sheet2!q" & reviewrow) = Range("sheet1!d31").Value
Range("Sheet2!o" & reviewrow) = Range("sheet1!g29").Value
Sheets("Sheet1").Select
Range("D9:G9,D11:G11,D13:G13,D15,G15,D21,G21,D24:G27,D5,D6,D31,G29,D29").Clearcontents
Range("d4").Select


With Worksheets("Sheet2").Visible = False
End With
Application.ScreenUpdating = True


End Sub


Now to answer your questions

• No, neither of the instances you mentioned are what I'm trying to do. Read it again.
• What I want - is for column X (which is currently BLANK) in sheet2, to become like a unique record label. You almost touched on it with the rownumbers, but that doesn't work for what I'm trying to do.

The whole project works like this (I was hoping to avoid this, but here we go

1. Users in Sheet1 enter data into fields.
2. A Command button runs the above macro which copies and pastes each field into the right column in Sheet2, which is a table.
3. Users are sent back to Sheet1 (hence the unhiding and hiding of sheet2 in the macro) where they can see the title of their entries in a listbox(which is simply referring to the table).
4. When they select an item from the listbox, they can delete or amend the entry (which then pastes the new data/deletes the data from the row of the table in sheet2)

Now #4 is a problem, because the second that you get something with the same title twice, if you want to delete the second one it will delete the first one in the table instead. Every month, out of the 200-400 entries that will go into the table, there will always be doubleups (which should not be removed, because they are individual entries, but will be used at different dates)

SO... To get around the problem of my other macro returning the wrong entry when it searches for something that happens to have a duplicate title somewhere, I want each cell in column x to be given a number when the above macro runs. This gives each row a unique identifier, by where I can later refer to that number where will return the data in the corresponding rows. Then it doesn't matter if I have two entries with identical names, because one of them would ahve for example "4" in column x on it's row, and the other might have "38" in it's row.

So what is needed is the equivalent of the function "=(X5)+1" in X6, "=(X6)+1" in X7, and so on and so on. But I want the macro to look at the cell above X5 to decide what to make X6. Other wise I would just manually type numbers in.

I hope that clears it up for people.

Cheers
C

 

[Cindy Ellis]

PLEASE enclose your code in code tags. THANK YOU for not changing it to tiny blue comic sans
If Column X is blank to start with, what value goes into X1? It's very easy to put an incremental value in each successive row, but if X1 is blank, it has to be initialized to something to be able to imcrement it.
In any case, why not just use

Range("Sheet2!X" & reviewrow) = Range("Sheet2!X" & reviewrow-1).value + 1

Time to leave for work, so I won't be able to reply for a few hours (I'll check back then).
Cindy

 

[ClimoC]

Hey Cindy


Thanks - I think what you've said there is pretty close to what I tried to put in but it failed me (I think I had the syntax wrong or something)

Oh and sorry but I didn't know what you meant about the [ ] bit, unless you meant to put a [ before the first word 'sub' and a ] after 'end sub'?

The first row of data starts at row 6 (in sheet2). What I will probably do is either put a 'space' in cells 1-5 in column x, then let that code run (or alternatively, 1-5) because all that matters is that I can use whatever value is in column X as a unique identifier to identify which row of data should be displayed/deleted by my other macros.

I will give this a try tomorrow and post back here if it was successful or not. Thanks for your help Cindy.

C

 

[Cindy Ellis]

Thanks for posting back. I guess I should have been more clear about the code tags. Before your code, you put the word CODE, surrounded by brackets "[ ]". After your code, you put /CODE, surrounded by the same brackets. So, it looks like "["CODE"]" your code here "["/CODE"]" except without the quotation marks. If I leave them off, they get interpreted as tags, and disappear
One way to do this more easily (in my opinion) is to go to your User Control Panel, (the UserCP link at the left of the MrExcel menu bar), then choose "Edit Options", scroll to the bottom, and change the editor selection to Standard Editor - Extra formatting Controls". Once you've done this, whenever you paste in code, you can just select all of the code, then click the # button at the top of the editor window, and it will add the tags.
Hope this helps,
Cindy