VBA Function syntax
I currently have an Access module that drops data into Excel and places the cursor below the last row of the last column. I want to programically place Excel's IRR function in this cell. Column A contains the dates and column C the amount field. Code snippet
' Selects c7; first blank cell at the bottom of a contiguous colum xlWs.Range("c1").End(xlDown).Offset(1, 0).Selec Thanks Forrest |
VBA Function syntax
IRR does not accept dates; XIRR does:
Selection.FormulaR1C1 = "=XIRR(R1C:R[-1]C,R1C1:R[-1]C1,0.1)" -- Regards, Tom Ogilvy Forrest wrote in message ... I currently have an Access module that drops data into Excel and places the cursor below the last row of the last column. I want to programically place Excel's IRR function in this cell. Column A contains the dates and column C the amount field. Code snippet: ' Selects c7; first blank cell at the bottom of a contiguous column xlWs.Range("c1").End(xlDown).Offset(1, 0).Select Thanks, Forrest |
VBA Function syntax
Tom, thanks for the syntax. I adjusted it to exclude row one which includes headers, but nothing drops in the Excel spreadsheet cell, so I hard coded the referenced cells; again nothing? What am I missing? I have included the entire code module up to your aditional line as modified per comments above. Any thoughts
Look forward to your feed-back as you recognized my need for the XIRR function Thanks Forres Private Sub Command0_Click( Dim cnt As New ADODB.Connectio Dim rst As New ADODB.Recordse Dim xlApp As Object ' Excel applicatio Dim xlWb As Object ' Excel workboo Dim xlWs As Object ' Excel workshee Dim strDB As Strin Dim fldCount As Intege Dim recCount As Lon Dim iCol As Integer 'Index variable for the current colum Dim iRow As Integer 'Index variable for the current ro strDB = "c:\FundSectors2002.mdb 'Open connection to the databas cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDB & "; 'Open recordset based on CashFlow1 tabl rst.Open "Select CashFlow1.Date, CashFlow1.Transaction, CashFlow1.Amount From CashFlow1", cn 'Open or create an instance of Excel and add a workboo On Error Resume Nex Set xlApp = GetObject(, "Excel.Application") If Err.Number < 0 The Err.Clear ' Clear Err object in case error occurred Set xlApp = CreateObject("Excel.Application") End I Set xlWb = xlApp.Workbooks.Ad Set xlWs = xlWb.Worksheets("Sheet1" 'Display Excel and give user control of Excel's lifetim xlApp.Visible = Tru xlApp.UserControl = Tru 'Copy field names to the first row of the workshee fldCount = rst.Fields.Coun For iCol = 1 To fldCoun xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Nam Nex 'Copy the recordset to the worksheet, starting in cell A xlWs.Cells(2, 1).CopyFromRecordset rs 'recCount = rst.RecordCount 'Attempt to capture record count and use as variable instead of End property xlWs.Range("c1").End(xlDown).Offset(1, 0).Select 'Selects c7, blank cell at bottom of contiguous colum xlWs.Selection.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" 'Tom's line modifie xlWs.Selection.FormulaR1C1 = "=XIRR(C2:C7,A2:A7,0.1)" 'Hard coded cell references |
VBA Function syntax
You don't need to select anyway:
xlWs.Range("c1").End(xlDown).Offset(1, 0).FormulaR1C1 = _ 'Selects c7, blank cell at bottom of contiguous column "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" 'Tom's line modified or, Selection is a property of the Application or the Window, but not the worksheet xlWs.Range("c1").End(xlDown).Offset(1, 0).Select xlApp.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" -- Regards, Tom Ogilvy "Forrest" wrote in message ... Tom, thanks for the syntax. I adjusted it to exclude row one which includes headers, but nothing drops in the Excel spreadsheet cell, so I hard coded the referenced cells; again nothing? What am I missing? I have included the entire code module up to your aditional line as modified per comments above. Any thoughts? Look forward to your feed-back as you recognized my need for the XIRR function. Thanks, Forrest Private Sub Command0_Click() Dim cnt As New ADODB.Connection Dim rst As New ADODB.Recordset Dim xlApp As Object ' Excel application Dim xlWb As Object ' Excel workbook Dim xlWs As Object ' Excel worksheet Dim strDB As String Dim fldCount As Integer Dim recCount As Long Dim iCol As Integer 'Index variable for the current column Dim iRow As Integer 'Index variable for the current row strDB = "c:\FundSectors2002.mdb" 'Open connection to the database cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strDB & ";" 'Open recordset based on CashFlow1 table rst.Open "Select CashFlow1.Date, CashFlow1.Transaction, CashFlow1.Amount From CashFlow1", cnt 'Open or create an instance of Excel and add a workbook On Error Resume Next Set xlApp = GetObject(, "Excel.Application") If Err.Number < 0 Then Err.Clear ' Clear Err object in case error occurred. Set xlApp = CreateObject("Excel.Application") End If Set xlWb = xlApp.Workbooks.Add Set xlWs = xlWb.Worksheets("Sheet1") 'Display Excel and give user control of Excel's lifetime xlApp.Visible = True xlApp.UserControl = True 'Copy field names to the first row of the worksheet fldCount = rst.Fields.Count For iCol = 1 To fldCount xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name Next 'Copy the recordset to the worksheet, starting in cell A2 xlWs.Cells(2, 1).CopyFromRecordset rst 'recCount = rst.RecordCount 'Attempt to capture record count and use as variable instead of End property? xlWs.Range("c1").End(xlDown).Offset(1, 0).Select 'Selects c7, blank cell at bottom of contiguous column xlWs.Selection.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" 'Tom's line modified xlWs.Selection.FormulaR1C1 = "=XIRR(C2:C7,A2:A7,0.1)" 'Hard coded cell references |
VBA Function syntax
Works lilke a champ. Thanks for the education.
|
VBA Function syntax
glad it worked - for completeness
xlWs.Range("c1").End(xlDown).Offset(1, 0).Select xlApp.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" should have been xlWs.Range("c1").End(xlDown).Offset(1, 0).Select xlApp.Selection.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" -- Regards, Tom Ogilvy Forrest wrote in message ... Works lilke a champ. Thanks for the education. |
All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com