Hello all. I'm brand new to this forum and a total VBA newbie so please let me know if I'm posting incorrectly and forgive my novice level of coding. I have attached (I think) a sample of data. Columns A thru N will be populated with a header line and varying amounts of lines of data, replaced each day as part of a larger Excel project. I am hoping to add a column to the right of the data which generates a unique identifier for each line of data. Basically, a formula that concatenates the date in column A, the symbol in column E, and a counter of the lines of data. The way I have tackled is by adding two columns, a "count" column and a "unique" column.
Although I'm sure there's a MUCH better/cleaner way to do it. Column O, the "Count", seems to work. But I am having trouble with Column N, the "Unique Identifier" because of the date imbedded in the formula. If I leave as below, the date in P2 is being converted to a 5 digit number. I also tried Range("P2") = TEXT(A2,"mmddyyyy")&E2&O2 like I would if I were inserting directly into Excel cell but I get a compile error. Assuming it has something to do with number of quotation marks because it tells me "Expected End of Statement" at "mmddyyyy". Is there a way to imbed the date formatting within this formula?
Here is what I have written:
Sub AddCountandUniqueIdentifier()
Sheets("Sheet1").Activate
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
' Adding a column that counts number of trades
Range("O1").Select
ActiveCell.FormulaR1C1 = "Count"
Range("O2") = 1
Range("O2:O" & LastRow).DataSeries , xlDataSeriesLinear
' Adding a column that creates unique identifier
Range("P1").Select
ActiveCell.FormulaR1C1 = "Unique"
Range("P2") = "=A2&E2&O2"
Range("P2:P" & LastRow).FillDown
End Sub
I am totally open to rewriting ALL of this or just addressing my date formatting issue. Thank you so much for any help and advice!