Syntax Correction: Combine text with variable

**cazooo** · 05-08-2009, 04:44 PM

I have the following VBA code and try to sum the range from B2 to CY2 for DA2; then B3 to CY3 for DA3, etc. Therefore I use the for loop. It results in "Application defined or Object defined error".

Could anybody help me to correct the syntax?

-----------------------------------------------------------------------

Sub CalculatePhi()
For i = 2 To 103
    Cells(i, 104) = Application.WorksheetFunction.Sum(Range("B &i& ", "CY &i& ")) / 101
    Cells(104, i + 1) = Application.WorksheetFunction.Sum(Range(Cells(i + 1, 2), Cells(i + 1, 103))) / 101
Next i
End Sub

**shg** · 05-08-2009, 04:47 PM

Please read the forum rules, and then edit your post to add code tags.

**shg** · 05-08-2009, 08:33 PM

Thanks.

This may fix the first line; I'm not sure what you're tring to do with the second.

Sub CalculatePhi()
    Dim i As Long
    
    For i = 2 To 103
        Cells(i, "DA") = WorksheetFunction.Sum(Cells(i, "B"), Cells(i, "CY")) / 101
        Cells(104, i + 1) = WorksheetFunction.Sum(Range(Cells(i + 1, 2), Cells(i + 1, 103))) / 101
    Next i
End Sub

**cazooo** · 05-08-2009, 08:44 PM

Thank you, it's solved. Actually it should have Range in between to sum from cell B2 to cell CY2 and the result should be pasted to cell CZ2 as shown in the code.

Sub CalculatePhi()
For i = 2 To 103
    Cells(i, "CZ") = WorksheetFunction.Sum(Range(Cells(i, "B"), Cells(i, "CY"))) / 101
    Cells(104, i + 1) = Application.WorksheetFunction.Sum(Range(Cells(i + 1, 2), Cells(i + 1, 103))) / 101
Next i
End Sub