Compile error on summing formula

[vba317]

I am trying to sum a column of data and I keep on getting an compile error. Expected. The formula I am trying to write is =C(iRw+1):C(iRw+16) to sum column C rows 1 - 16. I have highlighted the row that is giving me the problem. Any help would be apprecaited.

 For Z = 1 To rst.RecordCount
         
            With goXL.ActiveSheet
                .Cells(iRw, 2).Value = "'" & (rst![mon_nm]) & " " & (rst![Yr])
                .Cells(iRw, 3) = rst![1]
                .Cells(iRw, 4) = rst![2]
                .Cells(iRw, 5) = rst![3]
                .Cells(iRw, 6) = rst![4]
                'Excel formula =IF(ISBLANK(E5),0,F5/E5)
                .Cells(iRw, 7).Formula = "=if(ISBLANK(E" & (iRw) & "),0,F" & (iRw) & "/E" & (iRw) & ")"
                .Cells(iRw, 8) = rst![5]
                .Cells(iRw, 9) = rst![6]
                .Cells(iRw, 10) = rst![7]
                'Excel Formula =IF(ISBLANK(D5),0,I5/D5)
                .Cells(iRw, 11).Formula = "=if(ISBLANK(D" & (iRw) & "),0,I" & (iRw) & "/D" & (iRw) & ")"
                'Excel Formula =IF(ISBLANK(F5),0,I5/F5)
                .Cells(iRw, 12).Formula = "=if(ISBLANK(F" & (iRw) & "),0,I" & (iRw) & "/F" & (iRw) & ")"
                'Excel Formula =IF(ISBLANK(I6),0,I6/H6)
                .Cells(iRw, 13).Formula = "=if(ISBLANK(I" & (iRw) & "),0,I" & (iRw) & "/H" & (iRw) & ")"
                '=IF((I6+J6)=0,0,I6/(I6+J6))
                .Cells(iRw, 14).Formula = "=IF((I" & (iRw) & "+J" & (iRw) & ")=0,0,I" & (iRw) & "/(I" & (iRw) & "+J" & (iRw) & "))"
                .Cells(iRw, 15) = rst![8]
                'Excel Formula =IF(ISBLANK(O6),=O6/(AA6/AB6)
                .Cells(iRw, 16).Formula = "=if(ISBLANK(O" & (iRw) & "),0,O" & (iRw) & "/(AA" & (iRw) & "/AB" & (iRw) & "))"
                'Sum of column B for totals
                '=C(iRw+1):C(iRw+16)
         [Red]       .Cells(iRw, 17).Formula = ((C" &(iRw) &"+1)" & ":"  & "C" &(iRw) "&" "+16"))[/Red]
                .Cells(iRw, 27) = rst![9]
                .Cells(iRw, 28) = Daysper3Mon(rst![rptpd])
                
            End With
            rst.MoveNext
            iRw = iRw + 1
            If Not rst.EOF Then

 

[SkipVought]

hi,

The formula I am trying to write is =C(iRw+1):C(iRw+16) to sum column C rows 1 - 16.

'Sum of column [s]B[/s][b]C[/b] for totals
'=C(iRw+1):C(iRw+16)  [b]???[/b]

.Cells(iRw, 17).Formula = "=SUM(C1:C16)"

I don't think that you have stated your requirement clearly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vba317]

I am sorry, I will try and be clearer next time. I need the cells that are being calclated to use the iRw variable because sometimes the data is 16 rows other times it may be less if we havent completed the year yet. (the rows of data are the months in the year.)
That is why I am trying to put the variable irw in the formula.

 

[SkipVought]

In WHAT formula? This =C(iRw+1):C(iRw+16) is NOT an Excel formula!

Please put a FORMULA in your sheet that works and does what you want, and then COPY THAT FORMULA and paste it here.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vba317]

It is clear to me that I left out another part that can help you. The code I am doing is on access that I am exporting the data into a spreadsheet. I am trying to learn how to convert excel formulas into formulas that access recognizes. Here is a formula that is working in access with the excel formula above it.

'Excel Formula =IF(ISBLANK(O6),=O6/(AA6/AB6)
.Cells(iRw, 16).Formula = "=if(ISBLANK(O" & (iRw) & "),0,O" & (iRw) & "/(AA" & (iRw) & "/AB" & (iRw) & "))

 

[SkipVought]

BTW, what's the reason that you're coding formulas in VBA and not just directly on your sheet?

AND what version of Excel, please?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vba317]

Because that my boss wants me to do all the calculations in access and than export the results into an excal template and save the template as a worksheet. Im am using Acess and excel 2003.
Beginning
' OPEN EXCEL
Call XLCreate
If gbXLPresent = True Then
'******************************************************************************************
'*******************FIRST SHEET FOR TOTALS*************************************************
'******************************************************************************************
With goXL
.Workbooks.Open FileName:="C:\TestDatabases\AnestheticSolutions\AnestheticSolutions_2.xlt"
'Select Sheetname for information to go into.
.Sheets("ASOData").Select
End With

More Code Here

End of code
With goXL.ActiveSheet.PageSetup

.LeftMargin = goXL.Application.InchesToPoints(0.25)
.RightMargin = goXL.Application.InchesToPoints(0.25)
.TopMargin = goXL.Application.InchesToPoints(0.5)
.BottomMargin = goXL.Application.InchesToPoints(0.5)
.HeaderMargin = goXL.Application.InchesToPoints(0.25)
.FooterMargin = goXL.Application.InchesToPoints(0.25)
.LeftHeader = "Anesthetic Solutions, P.C."
.LeftFooter = Format(dtmDate, "dddd, mmmm dd, yyyy")
.RightFooter = "Pages " & "&P"
.Orientation = xlLandscape
.FitToPagesWide = 1
End With
With goXL.ActiveWorkbook
.SaveAs FileName:="C:\TestDatabases\AnestheticSolutions\ASO_" & (MonShortName(CurMon())) & "Total Report" & ".xls"
.Close
End With
Else
MsgBox "Can't create Excel Object", vbOKOnly, "Excel not found"
End If
' Close Excel Instance
Call XLKill
' Message it is closed
MsgBox "Reports Completed.", , "Done!"

End Sub

 

[PHV]

Something like this ?

.Cells(iRw, 17).Formula = "=SUM(C" & (iRw + 1) & ":C" & (iRw + 16) & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I have figured out the error of my ways. Thanks for helping

For RowCnt = 2 To 15
Call ConvColLet(RowCnt)
If RowCnt = 2 Then .ActiveSheet.Cells(iRw1, RowCnt).Value = "Totals" ' Puts Totals in col 2
If RowCnt = 2 Then .ActiveSheet.Cells(iRw2, RowCnt).Value = "Averages" 'Puts averages
.ActiveSheet.Cells(iRw1, RowCnt + 1).Formula = "=SUM(" & (ConvColLet(RowCnt + 1)) & (iBegRow) & ":" & (ConvColLet(RowCnt + 1)) & (iEndRow) & ")"
.ActiveSheet.Cells(iRw2, RowCnt + 1).Formula = "=AVERAGE(" & (ConvColLet(RowCnt + 1)) & (iBegRow) & ":" & (ConvColLet(RowCnt + 1)) & (iEndRow) & ")"
Next RowCnt