Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding problem (controlling word from excel)
Good morning,
having a problem with rounding errors when using Walkenbach Sub MakeMemos() how do i stop word from rounding the number from excel? for example: .TypeText Text:="Fee to be Deducted from Account:" & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & Format(Fee_deducted, "$##.##0") number shows as 1865.000 when it should be 1864.96 Thanks, For your help RTB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding problem (controlling word from excel)
I suspect you have Fee_deducted declared as long or integer. So it is being
rounded upon assignment. Declare it as single or double (or currency). the format function isn't the problem: fee_deducted = 1864.96 ? Format(Fee_deducted, "$##.##0") $1864.960 -- Regards, Tom Ogilvy "robert burger" wrote in message ... Good morning, having a problem with rounding errors when using Walkenbach Sub MakeMemos() how do i stop word from rounding the number from excel? for example: TypeText Text:="Fee to be Deducted from Account:" & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & Format(Fee_deducted, "$##.##0") number shows as 1865.000 when it should be 1864.96 Thanks, For your help RTB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding problem (controlling word from excel)
Tom,
bear w/ me. here's part of my code. Somehow i don't think its right. If i declare it as a currency then why am i giving the format as "$##.##0" Dim fee_deducted As Currency ' assign current data to variables fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.##0") ' send commands to word .TypeText Text:="Fee to be Deducted from Account:" & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & Format(fee_deducted, "$##.##0") *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding problem (controlling word from excel)
there is no need to introduce a format statement to assign the value to
fee_deducted. Currency is a number type - it doesn't require a $ or anything like that Sub BBB() Dim fee_deducted As Currency ActiveCell.Value = 1987.32 ' assign current data to variables fee_deducted = ActiveCell.Value ' send commands to word Debug.Print "Fee to be Deducted from Account:" & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & Format(fee_deducted, "$##.##0") End Sub produced: Fee to be Deducted from Account: $1987.320 -- Regards, Tom Ogilvy "robert burger" wrote in message ... Tom, bear w/ me. here's part of my code. Somehow i don't think its right. If i declare it as a currency then why am i giving the format as "$##.##0" Dim fee_deducted As Currency ' assign current data to variables fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.##0") ' send commands to word TypeText Text:="Fee to be Deducted from Account:" & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & Format(fee_deducted, "$##.##0") *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding problem (controlling word from excel)
Tom,
i guess what you mean by a fomat statement is: ' assign current data to variables fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.##0") You have me confused. I thought in order to have the number picked from excel and placed in word i need to assign current data to veriables? Anyways here is all my code so that you uderstand what i'm doing. Sub Makefeestatement() ' creates statments in word using automation Dim WordApp As Object ' start word and create an object Set WordApp = CreateObject("Word.Application") ' info from worskheet Set data = Sheets("sheet1").Range("a1") Message = Sheets("sheet1").Range("Message") ' cycle through all records in sheet1 Records = Application.CountA(Sheets("sheet1").Range("a:a")) For i = 1 To Records ' update status bar progress message Application.StatusBar = "processing record " & i ' assign current data to variables Port_account = data.Offset(i - 1, 0).Value For_Qtr_End = data.Offset(i - 1, 1).Value Port_name = data.Offset(i - 1, 2).Value Report_date = data.Offset(i - 1, 3).Value Avg_mnth_end_val = Format(data.Offset(i - 1, 4).Value, "##,000") Invst_mnge_fee = Format(data.Offset(i - 1, 5).Value, "##,000") GST = Format(data.Offset(i - 1, 6).Value, "#,000") fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.000") ' determine the file name SaveAsName = ThisWorkbook.Path & "\" & Port_account & ".doc" ' send commands to word With WordApp .Documents.Add With .Selection .typeparagraph .paragraphformat.Alignment = wdAlignParagraphJustify .InlineShapes.AddPicture Filename:="C:\Documents and Settings\rburger\Verity\verity logo\Logos + Branding Info\fee_logo_testing_color.jpg", _ LinkToFile:=False, SaveWithDocument:=True .Font.Size = 14 .Font.Bold = True .paragraphformat.Alignment = wdAlignParagraphJustify .TypeText Text:=vbTab & "Quarterly Statement of Fees" .typeparagraph .typeparagraph .typeparagraph .typeparagraph .Font.Size = 11 .paragraphformat.Alignment = 0 .Font.Bold = False .paragraphformat.Alignment = wdAlignParagraphRight .TypeText Text:="Report Date:" & vbTab & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & vbTab & Format(Date, "mmmm d, yyyy") .typeparagraph .typeparagraph .TypeText Text:="For the Quarter Ending: " & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & For_Qtr_End .typeparagraph .typeparagraph .TypeText Text:="Portfolio Name:" & vbTab & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & vbTab & Port_name .typeparagraph .typeparagraph .TypeText Text:="Portfolio Account:" & vbTab & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Port_account .typeparagraph .typeparagraph .TypeText Text:="Average Month End Portfolio Value:" & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Format(Avg_mnth_end_val, "$#,##.00") .typeparagraph .typeparagraph .TypeText Text:="Investment Management & Custody Fee:" & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Format(Invst_mnge_fee, "$#,#.00") .typeparagraph .typeparagraph .TypeText Text:="G.S.T at 7% (Reg:# unknown):" & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Format(GST, "$#,#.00") .typeparagraph .typeparagraph .Font.Underline = wdUnderlineSingle .TypeText Text:="Fee to be Deducted from Account:" & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Format(fee_deducted, "$##.##0") .typeparagraph .typeparagraph .typeparagraph .TypeText Message .typeparagraph .typeparagraph End With .ActiveDocument.SaveAs Filename:=SaveAsName ' .ActiveWindow.Close End With Next i ' kill the object WordApp.Quit Set WordApp = Nothing ' reset status bar Application.StatusBar = "" MsgBox Records & " fees were created and saved in " & ThisWorkbook.Path End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding problem (controlling word from excel)
I changed
fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.000") to fee_deducted = Format(Data.Offset(i - 1, 7).Value, "$##.000") and it worked fine for me. -- Regards, Tom Ogilvy "robert burger" wrote in message ... Tom, i guess what you mean by a fomat statement is: ' assign current data to variables fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.##0") You have me confused. I thought in order to have the number picked from excel and placed in word i need to assign current data to veriables? Anyways here is all my code so that you uderstand what i'm doing. Sub Makefeestatement() ' creates statments in word using automation Dim WordApp As Object ' start word and create an object Set WordApp = CreateObject("Word.Application") ' info from worskheet Set data = Sheets("sheet1").Range("a1") Message = Sheets("sheet1").Range("Message") ' cycle through all records in sheet1 Records = Application.CountA(Sheets("sheet1").Range("a:a")) For i = 1 To Records ' update status bar progress message Application.StatusBar = "processing record " & i ' assign current data to variables Port_account = data.Offset(i - 1, 0).Value For_Qtr_End = data.Offset(i - 1, 1).Value Port_name = data.Offset(i - 1, 2).Value Report_date = data.Offset(i - 1, 3).Value Avg_mnth_end_val = Format(data.Offset(i - 1, 4).Value, "##,000") Invst_mnge_fee = Format(data.Offset(i - 1, 5).Value, "##,000") GST = Format(data.Offset(i - 1, 6).Value, "#,000") fee_deducted = Format(Value.Offset(i - 1, 7).Value, "$##.000") ' determine the file name SaveAsName = ThisWorkbook.Path & "\" & Port_account & ".doc" ' send commands to word With WordApp .Documents.Add With .Selection .typeparagraph .paragraphformat.Alignment = wdAlignParagraphJustify .InlineShapes.AddPicture Filename:="C:\Documents and Settings\rburger\Verity\verity logo\Logos + Branding Info\fee_logo_testing_color.jpg", _ LinkToFile:=False, SaveWithDocument:=True .Font.Size = 14 .Font.Bold = True .paragraphformat.Alignment = wdAlignParagraphJustify .TypeText Text:=vbTab & "Quarterly Statement of Fees" .typeparagraph .typeparagraph .typeparagraph .typeparagraph .Font.Size = 11 .paragraphformat.Alignment = 0 .Font.Bold = False .paragraphformat.Alignment = wdAlignParagraphRight .TypeText Text:="Report Date:" & vbTab & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & vbTab & Format(Date, "mmmm d, yyyy") .typeparagraph .typeparagraph .TypeText Text:="For the Quarter Ending: " & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & For_Qtr_End .typeparagraph .typeparagraph .TypeText Text:="Portfolio Name:" & vbTab & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & vbTab & Port_name .typeparagraph .typeparagraph .TypeText Text:="Portfolio Account:" & vbTab & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Port_account .typeparagraph .typeparagraph .TypeText Text:="Average Month End Portfolio Value:" & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Format(Avg_mnth_end_val, "$#,##.00") .typeparagraph .typeparagraph .TypeText Text:="Investment Management & Custody Fee:" & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Format(Invst_mnge_fee, "$#,#.00") .typeparagraph .typeparagraph .TypeText Text:="G.S.T at 7% (Reg:# unknown):" & vbTab & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Format(GST, "$#,#.00") .typeparagraph .typeparagraph .Font.Underline = wdUnderlineSingle .TypeText Text:="Fee to be Deducted from Account:" & vbTab & vbTab & vbTab _ & vbTab & vbTab & vbTab & vbTab & Format(fee_deducted, "$##.##0") .typeparagraph .typeparagraph .typeparagraph .TypeText Message .typeparagraph .typeparagraph End With .ActiveDocument.SaveAs Filename:=SaveAsName ' .ActiveWindow.Close End With Next i ' kill the object WordApp.Quit Set WordApp = Nothing ' reset status bar Application.StatusBar = "" MsgBox Records & " fees were created and saved in " & ThisWorkbook.Path End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding problem (controlling word from excel)
Tom,
thanks again for your help. i realized my mistake was not matching the assign current data to variables to the send commands to word ie "$#,#,00" vs "$#,#.00" (the comma was wrong) Cheers, Rob *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controlling word wrap in a cell | Excel Discussion (Misc queries) | |||
Merging between word & excel my numbers not rounding correctly | Excel Discussion (Misc queries) | |||
Controlling Word from Excel | Excel Programming | |||
Controlling Word from Excel | Excel Programming | |||
Controlling Word template from Excel | Excel Programming |