Format doesn't stick

[Stuart Grant]

I have a fairly complex but not overlarge Workbook.

On one sheet Column Z is formatted with a custom format "d mmm yy".
Columns AA is formatted as Number, 2 decimal places, with a comma for 000s

In a macro I have
Range("Z7").Value = Worksheets(Transname).Range("A5").Value.
Range ("A5") has a date value formatted "d mmm yy" but in Z7 the correct
date appears formatted "dd/mmm/yyyy", a format which I never use and which
does not exist in the workbook.

Similarly in the macro there is
BalAmt = WorksheetFunction.VLookup(AccNum, Range("A746"),3)
Range("Z9").Value = BalAmt
The result of the lookup is a value formatted with two decimal places and a
comma. Once again the comma doesn't appear in Z9. It is formatted as a
General Number.

Why doesn't the formatting "stick" ? I have tried formatting the blank
cells correctly but it makes no difference.

Stuart

 

[Don Guillett]

Sometimes it helps to format the area first
or use format in your code
range("z7").value=format(xxx,yourformat)
or
range("z7").value=
range("z7").numberformat=

 

[Stuart Grant]

Thank you very much for your help. Formatting the area first does not work
but of course your two solutions do.
Stuart

 

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)

Thank you very much for your help. Formatting the area first does not
work but of course your two solutions do.
Stuart