When you turn on the macro recorder and format a cell as currency it is
recorded like this:

Selection.NumberFormat = "$#,##0.00"

"Currency" is not a valid argument to Numberformat.

--
Regards,
Tom Ogilvy

"Jon Delano" wrote in message
news:kHrkd.322122$wV.294228@attbi_s54...
Hello all (sorry for the long post)

I have some VB code in Excel XP (sp2). Windows XP Prof - sp2
It takes some data from a database and builds a spreadsheet, this works
great.

However, now I am trying to format the numbers.
I receive the error messagae about unable to set the number format.

I have searched the news groups and found a few items, such as protection,
excel 97 bug about the focus on the button.
None of these things are working for me.

Below is a complete listing of my code...

This sub is called with an account number, the account numbers are also
the
names of each sheet in the spreadsheet file.
Like I said, all the data goes in great, its when I hit the NumberFormat
line that I get the message.

I just added the select (because I read a thread where someone got this to
work when they did that.)
As you cantell, I'm total lost at this point, just trying this and that.

Any help at all would be great appreciated.

Thanks
Jon


Private Sub FillAccountSheet(SheetName As String)

Dim s As Worksheet
Dim x As Integer

For x = 1 To ThisWorkbook.Sheets.Count
If ThisWorkbook.Sheets(x).Name = SheetName Then
Set s = ThisWorkbook.Sheets(x)
Exit For
End If
Next x

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim fromDate As String
Dim toDate As String

fromDate = Sheet1.Cells(6, 3)
toDate = Sheet1.Cells(7, 3)

' open a connection to the database
With cn
.ConnectionString = "Provider=SQLOLEDB;data
source=*DATABASESERVER*;initial catalog=MMI;user id=*DBUSER*"
.Open
End With

s.Activate
s.Unprotect

' for the account specified (sheet name), group each vendors totals by
month and year with in the spreadsheet from and to dates
Dim SQL As String
SQL = "SELECT tblApVendor.Name, Period, [Year], " & _
"Sum([debitamt])-Sum([creditamt]) AS vendortotal " & _
"FROM tblGlJrnl INNER JOIN tblApVendor ON tblGlJrnl.Reference =
tblApVendor.VendorID " & _
"WHERE transdate = '" & fromDate & "' and transdate <='" & toDate
&
"' AND AcctId='" & Left(s.Name, 4) & "00000' " & _
"GROUP BY tblApVendor.Name, Period, [Year]"

rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly

Dim OldVendor As String
Dim VendorRow As Integer

OldVendor = ""
VendorRow = 5
Do While Not rs.EOF

If rs("Name") < OldVendor Then
VendorRow = VendorRow + 1
s.Cells(VendorRow, 1) = rs("Name")
OldVendor = rs("Name")
End If

Dim TotalCol As Integer
Dim MonthPeriod As Integer
Dim YearPeriod As Integer

TotalCol = 2

Do While s.Cells(4, TotalCol) < ""

' convert the date column titles to GL periods
If Month(CDate(s.Cells(4, TotalCol))) 9 Then
MonthPeriod = Month(CDate(s.Cells(4, TotalCol))) - 9
YearPeriod = Year(CDate(s.Cells(4, TotalCol))) + 1
Else
MonthPeriod = Month(CDate(s.Cells(4, TotalCol))) + 4
YearPeriod = Year(CDate(s.Cells(4, TotalCol)))
End If

If MonthPeriod = rs("Period") And YearPeriod = rs("Year") Then
Exit Do
Else
TotalCol = TotalCol + 1
End If
Loop

s.Cells(VendorRow, TotalCol) = rs("VendorTotal")
s.Range("B" & CStr(VendorRow) & ":" & Chr$(64 + TotalCol) &
CStr(VendorRow)).Select
s.Range("B" & CStr(VendorRow) & ":" & Chr$(64 + TotalCol) &
CStr(VendorRow)).NumberFormat = "Currency"
rs.MoveNext
Loop

' if there is data in the sheet
If VendorRow 5 Then
' add the sum function to the month columns
TotalCol = 2
Do While s.Cells(4, TotalCol) < ""
s.Cells(VendorRow + 2, TotalCol) = "=sum(" & Chr$(64 + TotalCol) &
"5.." & Chr$(64 + TotalCol) & CStr(VendorRow + 1) & ")"
TotalCol = TotalCol + 1
Loop

' add the vendor sums
For x = 6 To VendorRow
s.Cells(x, TotalCol) = "=sum(B" & CStr(x) & ".." & Chr$(64 +
(TotalCol - 1)) & CStr(x) & ")"
Next x

s.Cells(VendorRow + 2, TotalCol) = "=sum(" & Chr$(64 + TotalCol) &
"5.." & Chr$(64 + TotalCol) & CStr(VendorRow + 1) & ")"
End If

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing


End Sub