ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Unable to set the NumberFormat Property of the Range Class - ERROR (https://www.excelbanter.com/excel-programming/316342-unable-set-numberformat-property-range-class-error.html)

Jon Delano
Unable to set the NumberFormat Property of the Range Class - ERROR
 
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

Tom Ogilvy
Unable to set the NumberFormat Property of the Range Class - ERROR
 
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

keepITcool
Unable to set the NumberFormat Property of the Range Class - ERROR
 
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

Jon Delano
Unable to set the NumberFormat Property of the Range Class - ERROR
 
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" wrote in message
...
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


All times are GMT +1. The time now is 05:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com