VBA Number Formatting a Formula in Excel

user_14330577 0
Viewed 31

I'm hoping this is simple to do, but I have a vba script that auto populates a formula in my workbook.

Is it possible to make the output of this formula appear as mm/dd/yy ? I saw this snippet of code online, but can't get the syntax to work Range("A1").NumberFormat = "dd/mm/yyyy"

Any thoughts?

Sub addFormulas()
    With ThisWorkbook.Worksheets("Accounts")
    
        .Cells(4, 5).Resize(.Cells(.Rows.Count, 2).End(xlUp).Row - 1).Formula = "=IF('Accounts'!A4  ="""","""",VLOOKUP('Accounts'!A4,'MC'!A:R,18,0))"
    End With
End Sub
format vba excel date
1 Answers

Simply apply the number formats to the same range as the formula.

With ThisWorkbook.Worksheets("Accounts")
    With .Cells(4, 5).Resize(.Cells(.Rows.Count, 2).End(xlUp).Row - 1)
        .Formula = "=IF('Accounts'!A4  ="""","""",VLOOKUP('Accounts'!A4,'MC'!A:R,18,0))"
        .NumberFormat = "dd/mm/yyyy"
    End With
End With
user_99127140
Related