[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

[RobDog888]

You have your double quotes canceling out the evaluation of the variables.

oXLSheet.cells(j,5).Formula="=sum(" & oXLSheet.cells(j,2) & ":" & oXLSheet.cells(j,4) & ")"

But this will take the value of the cells and place it in the formula, I dont think this is what you want?

[RobDog888]

This should work for "=sum(B20:D20)"

oXLSheet.cells(j,5).Formula = "=sum(B" & j & ":D" & j & ")"

[engnouna]

Thanks RobDog888,that's it