I'm working on some code to export data from Access to Excel (via Access VBA) and then add some formulas to the worksheet. The export process is working great.
I then insert a formula over a range and format the numbers in that range, this also works great.
However, since my formula is in the cell 2 columns over from the last of the columns that is being summed, I'm getting the green warning triangle with the omitted cells warning. My current code is shown below. However the rng.Errors line raises an error:
1004 Application-defined or object-defined error
and then kicks me out of excel. Strangely, this also sets the visible property of the Excel object to False so I cannot see it in the quick launch bar, but it is visible in Task Manager.
Anybody have any ideas why this syntax is not working?
Public Sub Sum_Rows_and_Columns(StartCol As Integer)
Dim lngRowCount As Long
Dim lngColCount As Long
Dim strFormula As String
On Error GoTo ProcError
Set xl = GetObject(, "Excel.application")
Set wbk = xl.activeworkbook
Set sht = wbk.activesheet
lngRowCount = sht.usedrange.rows.Count
lngColCount = sht.usedrange.columns.Count
Set rng = sht.Range(sht.cells(2, lngColCount + 2), sht.cells(lngRowCount, lngColCount + 2))
strFormula = "=Sum(RC[" & StartCol - (lngColCount + 2) & "]:RC[-2]"
rng.Formula = strFormula
rng.NumberFormat = "#,###.00"
rng.Errors(5).Ignore = True '5=xlOmittedCells
sht.columns(lngColCount + 2).Entirecolumn.autofit
On Error Resume Next
Set sht = Nothing
Set wbk = Nothing
Set xl = Nothing
MsgBox "Sum_Rows_And_Columns" & vbCrLf & Err.Number & vbCrLf & Err.Description
Debug.Print "Sum_Rows_And_Columns", Err.Number, Err.Description