| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unable to set the FormulaArrary property of the range class | Excel Worksheet Functions | |||
| VBA error: Unable to set the Values property of the Series class | Charts and Charting in Excel | |||
| Error: Unable to get the OLEObjects property of the worksheet class | Excel Programming | |||
| Unable to set the colorIndex Property of the Interior Class Error | Excel Programming | |||
| Unable to set the Locked Property of the Range Class | Excel Programming | |||
Linear Mode
