For one date it works but for another throws error - Unable to set the NumberFormat property of the-CodePudding

I am using these statements to set format to mmm-dd format in some cells. When the MonthstartDate = 01-03-2022(or 01-06-2022) there is this error "Unable to set the NumberFormat property of the range class". But when the MonthStartDate = 01/07/2022 there is no error.

Really Strange for me!

Error occurs in the last statement.

''' Get Month start date
MthName = UI.Range("J21").Value
YearNumber = UI.Range("J22").Value
Set cm = Ref.Range("D9:D20").Find(MthName)
MonthNumber = cm.Offset(0, -1).Value
MonthStartDate = DateSerial(YearNumber, MonthNumber, 1)


wsGeographyRp.Range(ColLetter & "2").NumberFormat = xlGeneral
wsGeographyRp.Range(ColLetter & "2").Value = MonthStartDate
wsGeographyRp.Range(ColLetter & "2").NumberFormat = Format(MonthStartDate, "mmm-yy") ''' Error occurs here

Thanks for your help.

CodePudding user response：

NumberFormat takes a string. The result of Format is a string. When you pass different values to Format, you'll get different reults and therefore attempt to set different NumberFormats. Passing 01/07/2022 will result in format returning Jan-22 (VBA will think you're American).

With wsGeographyRp.Range(ColLetter & "2")
    .NumberFormat = xlGeneral
    .Value = CDate(MonthStartDate)
    .NumberFormat = "mmm-yy"
End With