Unable to set the NumberFormat Property of the Range Class - ERROR

J

Jon Delano

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
 
T

Tom Ogilvy

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 said:
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
Click to expand...
 
K

keepITcool

NumberFormat accepts the formatting string, not the name.
Most normal workbooks DO accept a style called "currency"


so it we combine it..(and want to avoid giving the cell the actual
currency style then..

activecell.numberformat =
activeworkbook.Styles("currency").NumberFormat

to just give the style..
activecell.style = "currency"


or the hard way..
activecell.numberformat = _
"_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jon Delano wrote :
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
Click to expand...
 
J

Jon Delano

Thank you ... that worked perfectly.
When I looked at the help file and it showed 'General', then I assumed that
it used all the named formats from the "format cells" dialog.

Thanks again
Jon

Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unable to set the Text Property of the Characters class 4
Error 1004 - unable to set the hastitle property of the axis class 14
Unable to set the bold property of the font class 6
Unable to set the LeftFooter property of the PageSetup Class 2
runtime error 9 out of range subscript 1
unable to get the activate property of the range class 6
runtime error 9 out of range subscript #2 6
Annoying Data Type Conversion Error 1

Top