[EXCEL & VBSCRIPT] How to insert a formula in a cell ?

[o.0]

Hello,

I'm desperately trying to insert a formula into a cell with vbs with no
luck.
I need some help.

Thanks a lot.

[JL]

Try This

Dim MsExcel, wb

Set MsExcel = CreateObject("Excel.Application")
MsExcel.Visible = True
Set wb = MsExcel.Workbooks.Add
With wb.worksheets(1)
.Range("A1").value = 1
.Range("A2").value = 2
.Range("A3").formula = "=SUM(A1:A2)"
End With
--
JL

Post by o.0
Hello,
I'm desperately trying to insert a formula into a cell with vbs with no
luck.
I need some help.
Thanks a lot.

[o.0]

Thanks for this but now can you tell me why this doesnt work ?

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
Set objWorkBook = objExcel.Workbooks.Open(PathCape)
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1)

Dim Row, Column, Cells, Content
Column = 2
Row = 5
Set Cells = currentWorksheet.Cells
Content = Cells(Row,Column).Value
DO
Column = Column + 1
Content = Cells(Row,Column).Value
LOOP Until Content = ""


Cells (18,Column).Formula = "=L(1)C" ****** HERE IS THE ERROR (Unknown
execution error btw)

[JL]

You need to use Row/Column referencing to reference cells in that way in a
formula. Here is my original now that bnow includes row/col indexing example.

.Range("A1").value = 1
.Range("A2").value = 2
.Range("A3").Formula = "=SUM(A1:A2)"
.Range("A4").FormulaR1C1 = "=R[-1]C"
--
JL

Post by o.0
Thanks for this but now can you tell me why this doesnt work ?
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkBook = objExcel.Workbooks.Open(PathCape)
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1)
Dim Row, Column, Cells, Content
Column = 2
Row = 5
Set Cells = currentWorksheet.Cells
Content = Cells(Row,Column).Value
DO
Column = Column + 1
Content = Cells(Row,Column).Value
LOOP Until Content = ""
Cells (18,Column).Formula = "=L(1)C" ****** HERE IS THE ERROR (Unknown
execution error btw)

[o.0]

Hmm the pb i got is that its not a fixed cell so i cannot use Range("A3")
because of the Column var.
Should i do this ?

Set ObjRange = objExcel.Range (objExcel.Cells(18, Column),
objExcel.Cells(18, Column))
objRange.FormulaR1C1 = "=L(1)C"

But i still got the same unknown error...dont understand !

[JL]

Two things that may be wrong:

1) Your using 'L(1)C' were I would expect 'R[1]C' - Note the bracket type
change!

If its not this then I suggest providing more info on what your trying to do.
--
JL

Post by o.0
Hmm the pb i got is that its not a fixed cell so i cannot use Range("A3")
because of the Column var.
Should i do this ?
Set ObjRange = objExcel.Range (objExcel.Cells(18, Column),
objExcel.Cells(18, Column))
objRange.FormulaR1C1 = "=L(1)C"
But i still got the same unknown error...dont understand !

[o.0]

OK.
I have a formula that refers to another sheet.
Since this formula will never be in a fixed column (actually it searches for
the next empty column), i'm trying to insert a formula accroding to the
Column var in several cells.

Example : if Column = 6
Then on row number 18 (this is a constant number) and Column = 6 the formula
would be : (french functions sorry,

=SI(RECHERCHEV(L(-13)C;Extraction.Compagnies.2005!L(-16)C(-5):L(4000)C(6);12
)="";"";RECHERCHEV(L(-13)C;Extraction.Compagnies.2005!L(-16)C(-5):L(4000)C(6
);12))

- RechercheV stands for Vertical Search
- SI stands for IF

On row 17 it would be (same Column) :

=SI(SI(RECHERCHEV(L(-12)C;Extraction.Compagnies.2005!L(-15)C(-5):L(4000)C(6)
;12)=2;"A";"J")<>L(-1)C;"";SI(RECHERCHEV(L(-12)C;Extraction.Compagnies.2005!
L(-15)C(-5):L(4000)C(6);12)=2;"A";"J"))

got like 10 lines to fill like that.