Format date within formula in VBA

[JenaB]

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!

[SamT]

Try this:
Range("P2").Formula = "=Text($A2,"yyyymmdd"),&$E2&$O2"
The reason for using yyyymmdd is that it sorts in chronological order

Using mmddyyyy will sort by months(1 to 12) then by days, then by years.
Jan 01
Feb01
.
.
Dec01
Jan02
Feb02
.
.
.Dec02
Jan03
Feb03
etc
Or some other weird pattern

What you are seeing when the result is numbers vice dates is Microsoft stores date as DateSerials, or the number of days counted since January 00, 1900. (Jan/1/1900 is DateSerial 1.) MS Applications Convert DateSerials to human readable Dates only for display to humans

[JenaB]

Unfortunately I'm met with the same Compile error - Expected: end of statement on that line.

[SamT]

Try remove the comma after the closing parentheses.
I expect the student to do their homework and find all the errrors I leeve in.

[Paul_Hossler]

Behind the scenes Excel stores date and times as Doubles.

Using Control+` to look at the real values and formulas

28456

Some suggestions

1. Always use Option Explicit at the top of the module

2. No need usually to Select or Activate objects, like Worksheets, Ranges, etc. if you can address them directly

3. Just using "Range(...)" by itself will refer to the ActiveSheet, which may not be what you want.
Use something like Worksheets("Data").Range("A1")

4. Your data was nicely arranged, so .CurrentRegion can be handy since it selects the block of cells

5. To get a quote in a VBA string like for making a formula, you need to use two of them

6. I added a hyphen between pieces of your Unique column



Option Explicit


Sub AddCountandUniqueIdentifier()
Dim r As Range

With Worksheets("Sheet1")
.Range("O1").Value = "Count"
.Range("P1").Value = "Unique"

Set r = .Cells(1, 1).CurrentRegion

.Range("O2").Value = 1
.Range("O2").Resize(r.Rows.Count - 1, 1).DataSeries , xlDataSeriesLinear
.Range("P2").Resize(r.Rows.Count - 1, 1).Formula = "=TEXT(A2,""yyyymmdd"")&""-""&E2&""-""&O2"
End With

End Sub

[SamT]

Thanks, Paul. I rarely work with WorkSheet Formulas in VBA

[snb]

You overcomplicate things by using VBA to do this.
You don't need column O.
Avoid 'Option Explicit' in VBA; it's redundant.
The formula is dependent of the international settings.
The formula below is for the US setting.

In P2, enter manually:


=TEXT(A2,"yyyymmdd ")&E2&COUNTIF(E$2:E2,E2)
And fill P3:Pn

If you'd insist to use VBA:


Sub M_snb()
With Sheet1.Cells(2, 16)
.Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
.AutoFill .Resize(200)
End With
End Sub

[Paul_Hossler]

1. You overcomplicate things by using VBA to do this.
2. You don't need column O.
3. Avoid 'Option Explicit' in VBA; it's redundant.
4. The formula is dependent of the international settings. The formula below is for the US setting.
5. In P2, enter manually:

And fill P3:Pn


6. If you'd insist to use VBA:


Sub M_snb()
With Sheet1.Cells(2, 16)
.Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
.AutoFill .Resize(200)
End With
End Sub




1. Possibly, but it's neater and more flexible

2. True, but 'helper' columns can avoid very long formulas and when you're learning can help show you what's happening

3. Opinion. I disagree, especially when you're learning. The same for letting everything default to a Variant instead of a strongly Typed variable

4. True, but Kentucky is part of the US and there's no indication that this has international use

5. Manual effort that has to be done every time the amount of data changes

6. Including COUNTIF( ..) in the formula could work, but .Resize(200) instead of using .CurrentRegion just adds formulas to empty rows.

The OP can decide which way they prefer to go

[snb]

No variables make 'Option explicit' the more redundant.
It serves no purpose to repeat your opinions.
Not bothering about variable types enhances the learning curve of anybody.
The amount of readers is hopefully bigger than the TS and hopefully from more countries than only the US.
To add to everybody's leaning curve: preferably use Excel's builtin options and avoid clumsy coding:


Sub M_snb()
With Sheet1.Cells(2, 16)
.Parent.ListObjects.Add 1, .CurrentRegion
.Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
End With
End Sub

PS. It's no use commenting the suggestions put here by other helpers.
Please restrict yourself to your own suggestions.
I don't consider this forum as a debating forum.

[Paul_Hossler]

No variables make 'Option explicit' the more redundant.
It serves no purpose to repeat your opinions.
Not bothering about variable types enhances the learning curve of anybody.
The amount of readers is hopefully bigger than the TS and hopefully from more countries than only the US.
To add to everybody's leaning curve: preferably use Excel's builtin options and avoid clumsy coding:

PS. It's no use commenting the sugestions put here by other helpers.
Please restrict yourself to your own suggestions.
I don't consider this forum as a debating forum.

What you call 'suggestions' are your opinions

When I use 'Option Explicit' and you say 'Don't use Option Explicit' I could take that as a comment on my suggestion

I agree this is not a debating forum, but not everyone has your years of experience so pontificating The Gospel of SNB without any explanations does not "enhance the learning curve of anybody"

I have nothing more to say (and we can all agree that's a good thing :thumb)

[JenaB]

Thanks, Paul. I rarely work with WorkSheet Formulas in VBA

Thanks for the attempt SamT but I didn't have any success with your suggestions. Just for closure, I was able to get this to work: Range("P2") = "=TEXT(A2,""mmddyyyy"")&E2&O2"

[JenaB]

.Range("P2").Resize(r.Rows.Count - 1, 1).Formula = "=TEXT(A2,""yyyymmdd"")&""-""&E2&""-""&O2"
[/CODE]

Paul_Hossler, your suggestions were INCREDIBLY helpful. Really, I learned so much by your notes and re-work on my codes. So that it is in my preferred format I rearranged the date format and removed the hyphens and get this code ("=TEXT(A2,""mmddyyyy"")&E2&O2") to work perfectly. Thank you so much!!

[JenaB]

You overcomplicate things by using VBA to do this.
You don't need column O.
Avoid 'Option Explicit' in VBA; it's redundant.
The formula is dependent of the international settings.
The formula below is for the US setting.

In P2, enter manually:


=TEXT(A2,"yyyymmdd ")&E2&COUNTIF(E$2:E2,E2)
And fill P3:Pn

If you'd insist to use VBA:


Sub M_snb()
With Sheet1.Cells(2, 16)
.Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
.AutoFill .Resize(200)
End With
End Sub

I do need to use VBA and hope to avoid any manual fill as this is part of a much larger spreadsheet with indirect formulas, etc. Nevertheless, thank you for your suggestions. I love the thought of not having to create column O and instead imbedding a "count" within the "unique" formula. Unfortunately, I'm not able to get your countif formula to work as I hoped. I'd like a simple 1, 2, 3, 4, 5 count of however many rows of data there are. Instead, this formula generates a new count each time there is a repeated item in column E. I could just bee looking at it incorrectly. Could you kindly take a look at my updated spreadsheet and see if you agree?

[Paul_Hossler]

Like this? I took out the hyphens, but for scanability, I formatted the 'count' with leading 0's

Although, it's not clear to me why you need it since just the row number would be a unique indentifier

28459




Option Explicit


Sub AddCountandUniqueIdentifier()
Dim r As Range

With Worksheets("Sheet1")
.Range("O1").Value = "Unique"

Set r = .Cells(1, 1).CurrentRegion

.Range("O2").Resize(r.Rows.Count - 1, 1).Formula = "=TEXT(A2,""yyyymmdd"")&E2&TEXT(ROW()-1,""0000"")"
End With

End Sub

[JenaB]

Like this? I took out the hyphens, but for scanability, I formatted the 'count' with leading 0's

Although, it's not clear to me why you need it since just the row number would be a unique indentifier

That worked perfectly. Thank you! I need a unique identifier bc I intend to reference it in a difference section of the project. For use in a vlookup or index match specifically. Does that make sense or am I going about it all wrong?

[Paul_Hossler]

bc I intend to reference it in a difference section of the project. For use in a vlookup or index match specifically. Does that make sense or am I going about it all wrong?

Well, one difficulty I can see is that since your Unique ID is based on a formula, if you sort or delete the data, then the ROW part of the formula and therefore the Unique ID will change.

That might not be what you want

[JenaB]

That makes total sense. Thank you again to all of you who have helped me on my first forum post. I'll mark as solved at this time.

[SamT]

if you sort or delete the data, then the ROW part of the formula and therefore the Unique ID will change.
You can make the Unique ID Static by changing

.Range("P2").Resize(r.Rows.Count - 1, 1).Formula = "=TEXT(A2,""yyyymmdd"")&E2&O2"

To

For rw = 2 to r.Rows.Count
If Cells(rw, "P") = "" Then _ 'If P is empty Then... Space&Undersore = Line continuation; No End If Required for single line If...Thens
Cells(rw, "P") = Format(Cells(rw, "A"), "mmddyyyy") & Cells(rw, "E") & Cells(rw. '"O") 'Add Static Unique ID
Next rw

However, I still recommend using the international style Date Formatting "yyyymmdd". It prevents programmatic confusion about dates like 04052021 (Apr 05, 2021 or 04 May, 2021)

[Paul_Hossler]

Some things to think about

1. Instead of a unique identifier (which might cause problems) you could use a UDF (User Defined Function) to calculate values for reporting. Not as fast as using the intrinsic Excel worksheet functions, but something to consider

2. Not clear what your end objective is, but Pivot tables can answer a lot of questions and have many formatting options

28462