Formatting Date as Text and keeping dd-mmm-yyyy format

[TXRChav]

Hello,

Within a file that is the output of another macro I have a date column. The column has to be in text format as it is intended to be imported into another software that does not like receiving the excel date equivalent value (i.e. 1 represents 1/1/1900). I need to keep the format of dd-mmm-yyyy, but the value in the code is just the equivalent integer.

I have already tried to use

Selection.NumberFormat = "@"

in my vba, but this just converts the day into the equivalent integer in text format. I know the formula

=TEXT(A1,"dd-mmm-yy")

but would prefer not to have to enter the formula in a new column and then cut and copy back into the original column. Does anyone know how to use the vba format code equivalent to the formula to change the value in the cells directly?

 

[shg]

  With range("A1")
    .NumberFormat = "@"
    .Value = Format(.Value, "dd-mm-yyyy")
  End With

 

[TXRChav]

Awesome. Thanks shg.

 

[shg]

You're welcome.