jwise
04-19-2007, 03:11 PM
I'm having a problem with VBA's FORMAT routine. Here's the code, expunged of the real stuff:
Sub fmtData()
'
' Code produces incorrect output
'
Cells(1, 2) = Format(Cells(1, 1), "$###,##0") 'OK
Cells(2, 2) = Format(Cells(2, 1), "$###,##0") 'OK
Cells(3, 2) = Format(Cells(3, 1), "mmm yyyy") 'Here is the problem
Cells(4, 2) = Format(Cells(4, 1), "mmm-yyyy") ' And here
Cells(6, 2) = Format(Cells(6, 1), "0.00%") 'OK
End Sub
Please put the following in the cells:
A1 = 123456
A2 = 123
A3 = 3/10/2006
A4 = 4/28/2007
A6 = "=5/8"
I am only concerned with A3 and A4 which are dates. On this XL2000, my result is "1Mar" for A3 (in cell B3) and "1Apr" for A4 (in cell B4). The dollars test in A1 and A2 works as expected, and so does the percentage in A6.
Any ideas as to what I've done wrong?
Update: I copied the code out of VBE and into a regular text file. Then I wrote this query. To test it again, I did the whole thing over (after stopping and starting XL2000 again.
It worked as described on the second try. What gives?
Sub fmtData()
'
' Code produces incorrect output
'
Cells(1, 2) = Format(Cells(1, 1), "$###,##0") 'OK
Cells(2, 2) = Format(Cells(2, 1), "$###,##0") 'OK
Cells(3, 2) = Format(Cells(3, 1), "mmm yyyy") 'Here is the problem
Cells(4, 2) = Format(Cells(4, 1), "mmm-yyyy") ' And here
Cells(6, 2) = Format(Cells(6, 1), "0.00%") 'OK
End Sub
Please put the following in the cells:
A1 = 123456
A2 = 123
A3 = 3/10/2006
A4 = 4/28/2007
A6 = "=5/8"
I am only concerned with A3 and A4 which are dates. On this XL2000, my result is "1Mar" for A3 (in cell B3) and "1Apr" for A4 (in cell B4). The dollars test in A1 and A2 works as expected, and so does the percentage in A6.
Any ideas as to what I've done wrong?
Update: I copied the code out of VBE and into a regular text file. Then I wrote this query. To test it again, I did the whole thing over (after stopping and starting XL2000 again.
It worked as described on the second try. What gives?