Forum Discussion
VBA Range Format
VBA,
F2:F99999 = date format = 18-sept-21
"Range("F2:F99999").Numberformat = "yyyy-mm-dd"" works and format come as 2021-09-18 as custom
but then changes format after closing excel. when i reopen excel the format becomes date. and transfer program need string to transfer the data.
Format intended = custom format
Format after closing and reopening excel = Date
how to fix this and why this is happening?
WAW nice! Bingo!
Explain.. :
.formula means what ?
the IF is an IF excel function? if so, then F2="""" is the logical test ? does it mean if empty? why 4 of "
then , """" means value if true? and then ,TEXT(F2,""yyy-mm-dd"") if value if false?
.Numberformat = "@" will format G column as TEXT
.value (G2) = .value (F2) <-- how does it know this is F2 ?
end with (end loop)
sorry for all the questions but i want to understand it.
Thank you so much
8 Replies
Sorry HansVogelaar , still showed as no replies when I started.
Fchedraoui Are you sure about the apostrophe "'"? Excel normally ignores it but, as Hans suggests the text number format "@" should also work.
- I tested it in a sheet and it seemed to work fine. I tested inside a table, inside a spill range, regular values and it saved the custom format in every case.
Since it is a macro enabled file (I assume) is it possible there is some other macro that runs on opening/closing/etc... that is overriding the custom formatting? I am guessing somewhat, but I suspect the problem is that the column contains datevalues and though applying a number format creates the correct appearance in the range, it is still a number and will be read as such. My knowledge of VBA is somewhat sketchy, but maybe something of the nature
Option Explicit Option Base 1 Sub ToText() ' ' ToText Macro ' Dim v As Variant Dim i As Long, n As Long Dim t() As Variant Dim rng As Range ' Set rng = Range("dates") v = rng.Value n = UBound(v, 1) ReDim t(n, 1) For i = 1 To n t(i, 1) = "'" & Application.WorksheetFunction.Text(v(i, 1), "yyyy-mm-dd") Next i rng.Value = t End Submight provide a start.
yyyy-mm-dd is a Date format. Applying does not change the values to text, it only changes the way they are displayed. You might populate another column with text values:
With Range("G2:G99999") .Formula = "=IF(F2="""","""",""'""&TEXT(F2,""yyy-mm-dd""))" .Value = .Value End WithHere is the result:
'2021-03-14
I cannot have ' in the beginning, is there a work around?
the funny part is if i go to excel and i select all data except title of the F column and then right click format custom and choose yyyy-mm-dd it works no problem. it stays as custom.How about
With Range("G2:G99999") .Formula = "=IF(F2="""","""",TEXT(F2,""yyy-mm-dd""))" .NumberFormat = "@" .Value = .Value End With
