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 

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 

    • mtarler's avatar
      mtarler
      Silver Contributor
      Apr 05, 2022

      PeterBartholomew1  I had the same thing happen with it not showing the replies

      HansVogelaar  Glad you caught the need

      Fchedraoui  .formula will set what the 'formula' in that cell is set to.  

         The duplicate "" is basically escape character to tell excel to actually send a " instead of the " meaning the following characters are part of a string so 

      "=IF(F2="""","""",TEXT(F2,""yyy-mm-dd""))"

       should look like

      =IF(F2="","",TEXT(F2,"yyy-mm-dd"))

      inside the cell on the sheet itself  

      yes the .numberformat="@" is text

      as for the .value=.value it is not G2 = F2 and I suspect this doesn't really do anything since it is really the .formula that matters.

       

Resources