Forum Discussion

Fchedraoui's avatar
Fchedraoui
Copper Contributor
Apr 05, 2022
Solved

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 reop...
developer
excel
Macros and VBA
  • Fchedraoui's avatar
    Fchedraoui
    Apr 05, 2022

     

    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

    HansVogelaar 

HansVogelaar's avatar
HansVogelaar
MVP
Apr 05, 2022

Fchedraoui 

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 With
  • Fchedraoui's avatar
    Fchedraoui
    Copper Contributor
    Apr 05, 2022

    Here 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. 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Apr 05, 2022

      Fchedraoui 

      How about

          With Range("G2:G99999")
        .Formula = "=IF(F2="""","""",TEXT(F2,""yyy-mm-dd""))"
        .NumberFormat = "@"
        .Value = .Value
    End With
      • Fchedraoui's avatar
        Fchedraoui
        Copper Contributor
        Apr 05, 2022

         

        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

        HansVogelaar 

Resources