CODEWITHSUNDEEP
vbaexcel
anon
anon

Reputation:

how to add date after text in a cell

I have a cell B8 that is currently formatted with the Currency Format, in a sheet that we will simply call Sheet1. I am sure this is something that I will need to accomplish in VBA, but what would be the process of having Excel add the following to the end of the cell B8:

as of 'MM/DD/YY' where the date is today (not concerned if the date auto updates or not, such as if I were to save the workbook after VBA entering today's date and if I were to reopen tomorrow it could either show the original date or the new date - does not make a difference as it will get saved in a non editable format)

So with consideration that the cell is formatted with the Currency format (which excludes decimals btw), by me entering a number such as "10000", the cell should display the following:

$10,000 as of 03/03/16

Any suggestions?

Upvotes: 0

Views: 1980

Answers (2)

user4039065
user4039065

Reputation:

You can retain the raw numerical value typed into B8 while displaying the currency symbol and decimal places as well as the 'as of mm/dd/yy' portion but the date will be display only.

Copy this code and paste it into the worksheet's code sheet (right-click the ws name tab, View Code).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B8")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Dim str As String
        str = Range("B8").NumberFormat
        If CBool(InStr(1, str, "\a\s of", vbTextCompare)) Then _
            ThisWorkbook.DeleteNumberFormat NumberFormat:=str
        str = Format(Date, " \a\s of dd-mmm-yyyy")
        str = StrConv(str, vbUnicode)
        str = Join(Split(str, vbNullChar), Chr(92))
        str = Left(str, Len(str) - 1)
        str = "$#,##0" & str & ";[Red]($#,##0)" & str
        Range("B8").NumberFormat = str
        'Debug.Print str
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

You will not build up a number of useless custom number formats because each is deleted before assigning a new one; even if it is the same one. The value in the B8 cell remains available for calculation and comparison.

        cnf_as_of

Note the number-like right-alignment in the cell. If the column is not wide enough to display the entire blurb, you will end up with ########.

Upvotes: 2

David Zemens
David Zemens

Reputation: 53663

Select any cell and use a macro like this to apply the number format with today's date. This format is static and will not change (unless you re-run the macro).

I derived this from a recorded macro, where I used a "Custom" format and applied a simple string to it, like so:

enter image description here

That gives me some basic code like:

Selection.NumberFormat = "$#,##0_)"" foo"";[Red]($#,##0)"" foo"""

Note the double quotation marks. That part is a little tricky to work with sometimes. Then modified it for this criteria:

Sub Macro2()
'
' Macro2 Macro
Dim dt As String
dt = """ as of " & Format(Now(), "[$-409]mmmm d, yyyy;@") & """"

    Selection.NumberFormat = _
        "$#,##0_)" & dt & ";[Red]($#,##0)" & dt
End Sub

Upvotes: 1

Related Questions