With / End With:- Changing property of the Variable to be in date Format or Number format

[SPiercy]

Hi,

Im currently using With / End With statements to apply formulas or link in data via a named range.

What I want to be able to do is change the property of what I'm linking in to be in date format.

For example I have the code
Set FiscalYrPrd = Range("AG7:AH" & .Range("AA" & .Rows.Count).End(xlUp).Row)
With FiscalYrPrd
.Formula = "=C7"
.Value = .Value
End With

my understanding if that your changing the property of the variable to be a formula and then a value, therefore is it possible to change the format property of the range so its in date format (dd/mm/yy)?

Any help would be greatly appreciated!
In addition - same request but in numerical format with zero d.p?

Many thanks in advance.

Rgd,

SPiercy

 

[VoG]

Try

With FiscalYrPrd
    .Formula = "=C7"
    .Value = .Value
    .NumberFormat = "dd/mm/yy"
End With

 

[SPiercy]

Thanks for that.
And for a number to be in the format 1234 (i.e no dp and no thousand seperator)
it would be
.Numberformat = "0"
Is this correct.

 

[VoG]

Yes, that should work. The easiest way to get number format code is to record a macro whilst changing the format manually.

 

[SPiercy]

Thanks for your help