.NumberFormat not getting applied?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PeteCresswell - 17 May 2008 16:51 GMT

---------------------------------------------------------------------------------
Const mFormat_DollarAmount As String = "#,##0.00"

4140 With .Columns(mColNum_FP_AmountOutstanding)
4141 .ColumnWidth = 15
4142 .NumberFormat = mFormat_DollarAmount
4143 .HorizontalAlignment = xlRight
4149 End With
---------------------------------------------------------------------------------

The width and horizontal alignments are working, but
the .NumberFormat is not.

Same problem with date columns:
---------------------------------------------
Const mFormat_Date="mm/dd/yyyy"
.NumberFormat = mFormat_Date
--------------------------------------------

Some limitation on what I can do at the column level?

Reply to this Message

JLGWhiz - 17 May 2008 17:27 GMT

Can't duplicate the problem. Code works fine for me.

> ---------------------------------------------------------------------------------
> Const mFormat_DollarAmount As String = "#,##0.00"
[quoted text clipped - 16 lines]
>
> Some limitation on what I can do at the column level?

Reply to this Message

Dave Peterson - 17 May 2008 17:36 GMT

Any chance that you're looking at the wrong column??? You didn't show what that
.columns(...) referred to.

And two wild guesses...

Do you have merged cells in that range?
What's the style for that range? Is it something special or is it Normal?

Does the code work on a brand new worksheet?

Signature

Dave Peterson

Reply to this Message

Ron Rosenfeld - 17 May 2008 17:55 GMT

>---------------------------------------------------------------------------------
> Const mFormat_DollarAmount As String = "#,##0.00"
[quoted text clipped - 16 lines]
>
>Some limitation on what I can do at the column level?

You don't post much of your code, so it's hard to know where the problem is.

Some of the possibilities include an improper declaration of the columns
property; use of this code snippet with a function (it would need to be in a
sub).

Here is a short routine demonstrating several different ways of setting formats
for an entire column, none of which match what you have written:

=============================
Option Explicit
Sub colfmt()
Dim c As Range
Set c = Range("a1")
c.EntireColumn.NumberFormat = "mm/dd/yyyy"
Columns(2).NumberFormat = "mm/dd/yy"

With Columns(3)
.NumberFormat = "mm/dd/yy"
.ColumnWidth = 25
.HorizontalAlignment = xlCenter
End With
End Sub
==================================
--ron

Reply to this Message