[RESOLVED] writing formulas from VB6

[engnouna]

Hi
I am adding formula to an excel worksheet.
I am using the following code

Dim wkbSource As Workbook
Dim oXLSheet As Worksheet
Dim j As Integer
 
 
Set wkbSource = Workbooks.Open("C:\test\test.xls")
 
Set oXLSheet = wkbSource.Worksheets("Stat")
 
sKey = InputBox("Enter the name of the Key that u want to add", "KeyManagement")
 
lastRow = oXLSheet.UsedRange.End(xlDown).Row
    j = lastRow + 1
    
oXLSheet.Cells(j, 1).Value = sKey
oXLSheet.Cells(j, 2).Formula = "=SUMPRODUCT(COUNTIF(INDIRECT(" & Chr(34) & "'Sheet1'!L2 : L1000" & Chr(34) & ")," & Chr(34) & "*" & sKey & "*" & Chr(34) & "))"
oXLSheet.Cells(j, 3).Formula= "=SUMPRODUCT(COUNTIF(INDIRECT(" & Chr(34) & "'Sheet2'!L2 : L1000" & Chr(34) & ")," & Chr(34) & "*" & sKey & "*" & Chr(34) & "))"
oXLSheet.Cells(j, 4).Formula= "=SUMPRODUCT(COUNTIF(INDIRECT(" & Chr(34) & "'Sheet3'!L2 : L1000" & Chr(34) & ")," & Chr(34) & "*" & sKey & "*" & Chr(34) & "))"

let's take an example,let's take sKey="Sports" and j=20 so i want to write a formula into cell E20, the formula is "=sum(B20:D20)"

I tried writing:

oXLSheet.cells(j,5).Formula="=sum(cells(j,2):cells(j,4))"but it didn't work
so how can i do it?
thanks