Formula in Excel via Access/VBA (XP SP 2)

[kwvh]

I am attempting to insert a formula in a spreadsheet via vba. I recorded a macro in Excel, copied, pasted and made some simple edits. However, I am getting an error when trying to insert the formula (.ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-2],RC[-2],RC[-1])")

Can anyone point me in the right direction?

Function CreateSpreadsheet(strSourceName As String, strFileName As String, strWorksheet As String)
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
Dim xlApp As Excel.Application
On Error GoTo Err_CreateSpreadsheet

' Create an Excel workbook file based on the
' object specified in the second argument.
'THIS CREATES THE SPREADSHEET
' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strSourceName, strFileName, False

DoCmd.OutputTo acOutputReport, strSourceName, acFormatXLS, strFileName

' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")

' Open the spreadsheet to which you exported the data.

Set xlWrkbk = xlApp.Workbooks.Open(strFileName)

' Determine the size of the range and store it.
Set xlSourceRange = xlWrkbk.Worksheets(strWorksheet).Range("a1").Curre ntRegion

xlWrkbk.Worksheets(1).Cells.Select
''''''''''''''''''''''''
With xlWrkbk.Worksheets(1)
With .Cells.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic

End With
Dim r As Long
' Max row number
Dim m As Long
Dim Cell As String
' Last row
m = .Range("A65536").End(xlUp).Row
r = m
Cell = "I" & r
.Range("I2").Select
' Error on following line ugh!
.ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-2],RC[-2],RC[-1])"
' Object doesn't support this property or method
.Selection.AutoFill Destination:=Range("I2:" & Cell), Type:=xlFillDefault

.Range("A2").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9, 10, 12, 13, 14, 16, 17)
.Columns.AutoFit

'Delete second total row
m = .Range("A65536").End(xlUp).Row
r = m
.Range("A" & r).EntireRow.Delete
End With

''''''''''''''''''''''''''''''''''''''''''''''''
Exit_CreateSpreadsheet:

Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
' Close and save the workbook
xlWrkbk.Close SaveChanges:=True
Set xlWrkbk = Nothing
' Quit Excel
xlApp.Quit
Set xlApp = Nothing
Exit Function

Err_CreateSpreadsheet:
MsgBox CStr(Err) & " " & Err.Description
Resume Exit_CreateSpreadsheet

End Function


Thanks,
Ken

[HansV]

Try replacing

.Range("I2").Select
' Error on following line ugh!
.ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-2],RC[-2],RC[-1])"

with

.Range("I2").FormulaR1C1 = "=IF(RC[-1]>RC[-2],RC[-2],RC[-1])"

or with

.Range("I2").FormulaR1C1 = "=MIN(RC[-2],RC[-1])"

[kwvh]

Thanks Hans!

Worked very well.