Hi Monika,

Struggling a bit to see what is happening without any data, but is this what
you want

Cells(j, colEnd).FormulaR1C1 = "=SUMPRODUCT(--(RC" & lastCol & ":RC"
& colEnd - 1 & ")=" & sysYr & ")"
Cells(j, colEnd + 1).FormulaR1C1 = "=SUMPRODUCT((LEFT(RC" & lastCol
& ":RC" & colEnd - 1 & ",1)=""Q"")*" & _
"(RIGHT(RC" & lastCol & ":RC" &
colEnd - 1 & ",4)=""" & sysYr & """))"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"monika" wrote in message
...

HI,

My column heading (Y9:AF9) a
Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum
Year 2005
Sum

I have to have a YEar SUm of 2004 and 2005, which i am
doing by the code
below. With this logic i am getting the final value and
formala. Instead of
this logic I want to have =SUM(Y10:AC10) in each cell.
Before having a total
I want to check whether it lies in 2004 or 2005. IS it
possible to replace
the cell value by the formula as =SUM(Y10:AC10)?

thanks in advance
monika

'INSERTING VALUES FOR YEARLY SUM'S COLUMNS
Dim sysYr, fSumYr1, fSumYr2 As Long
Dim QYR As Long
sysYr = Year(Date)
j = LNewRng.Row + 1
k = LNewRng.Row

While j <= LastCellNum
i = lastCol + 1 'REINITIALIZING FOR EVERY
NEW ROW
fSumYr1 = 0 'REINITIALIZING FOR EVERY
NEW ROW
fSumYr2 = 0 'REINITIALIZING FOR EVERY
NEW ROW
While i <= colEnd
If IsDate(Cells(k, i).Value) = True Then
'SUM FOR DATE CASES
yr1 = Year(Cells(k, i).Value)
If yr1 = sysYr Then
fSumYr1 = fSumYr1 + Cells(j, i).Value
Else
'Stop
fSumYr2 = fSumYr2 + Cells(j, i).Value
End If
ElseIf Mid(Cells(k, i).Value, 1, 1) = "Q" Then
'QUARTER SUM CALCULATION
QYR = Right(Cells(k, i).Value, 4)
If QYR = sysYr Then
fSumYr1 = fSumYr1 + Cells(j, i).Value
Else
fSumYr2 = fSumYr2 + Cells(j, i).Value
End If
End If
i = i + 1
Wend
Cells(j, colEnd).Value = fSumYr1
Cells(j, colEnd + 1).Value = fSumYr2
j = j + 1
Wend