Formatting Cells in VBA

[lee2smooth03]

Hello All,

I'm working on a function that is supposed to compare text. The way that it works is simple: the function takes in a data range as one of its input variables; the range is set up so​

that the first column is a custom date format ("mm/dd/yyyy")...in case you didn't notice, that date format is a custom date format, not one that is built into excel. I'm a bit of a stickler for formatting and I cannot stand to see misalignment in varying dates... . I want them all aligned...maybe a bit OCD, but that's why I like programming

Take a look at the code below:

Function countByDay(theData As Range, item As Integer) As Integer

Dim rowCount As Integer
Dim colCount As Integer

'For rowCount = 1 To Range("theData").CurrentRegion.Rows.Count
For rowCount = 1 To theData.CurrentRegion.Rows.Count
    Cells(rowCount, 1).Value = rowCount + item              'test procedure that validates the loop
    'Cells(rowCount, 2).Value = theData.Cells(rowCount, 1)   'test procedure that validates the loop
    
    Cells(rowCount, 2).Value = Application.WorksheetFunction.Text(theData.Cells(rowCount, 1), "mm/dd/yyyy")
    
Next rowCount
End Function

So far, the loop works well (I'm still relatively new to VBA programming so producing a successful loop counts as WIN #1), but my problem is the last line:

Cells(rowCount, 2).Value = Application.WorksheetFunction.Text(theData.Cells(rowCount, 1), "mm/dd/yyyy")

I want my loop to output the date in "mm/dd/yyyy" format, but when I see the results in the excel grid, they appear in "m/dd/yyyy" format at best. How do I get the "application.worksheetfunction.text" function to give my dates leading zeroes?

Thanks for all of your help!

 

[JoeMo]

Here's a snippet you can adapt that produces the format you want if the format of the selected cells is any valid date format (e.g. m/d/yy):

Sub CustomDate()
With Selection.Offset(0, 1)
    .Value = Selection.Value
    .NumberFormat = "mm/dd/yyyy"
End With
End Sub

This leaves the result as a date (number) rather than text.

 

[lee2smooth03]

Thanks! That works great!
I don't know why I didn't think of using a "With" statement before...